Solved

Need help with MS SQL Select Distinct Query - multiple columns

Posted on 2010-09-20

1 solution

444 Views

Last Modified: 2012-05-10

I have the query below:

select distinct column0, id
from all_codes
group by id,column0

The ID field is an auto increment field and is necessary to keep the codes in cloumn0 in a specific order.

If I run this query it does not select unique records from Column0, but returns all records. (There are about 20,000 duplicates in column0 out of total 1 million records)

I need to return only unique records in column0 but keep the sort on the ID field.

Sample data shown below:
Column0 ID
PB-2760      1
LB-2662      2
YO-0215      3
JY-5620      4
BK-3187      5
WY-8750      6
KL-5024      7
FB-2647      8
JO-9972      9
TY-4709      10

I can use the query:

select distinct column0 from codes_all

but then SQL messes up the order of column0 - I need it in the order in which it was imported in to the table.

Using MS SQL 2005 Express.

Help needed ASAP

Thanks!

Comment

Question by:TrentSlater

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

10 Comments

LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]

ID: 337153942010-09-20

you want this:

select column0, id
 from (
  select column0, id, row_number() over (partition by column0 order by id) rn
    from all_codes
 ) sq
where rn = 1

Open in new window

Author Comment

by:TrentSlater

ID: 337154162010-09-20

That works fine to remove the duplicates but it is sorting by ID, not column0. I need it sorted by column0

LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]

ID: 337154192010-09-20

this should do it:

select column0, id
 from (
  select column0, id, row_number() over (partition by column0 order by id) rn
    from all_codes
 ) sq
where rn = 1
order by column0

Open in new window

LVL 2

Expert Comment

by:k_romych

ID: 337154312010-09-20

SELECT Column0, ID FROM all_codes
GROUP by Column0
ORDER BY id

Author Comment

by:TrentSlater

ID: 337154322010-09-20

Sorry - still sorting by ID.

Could we copy these into a new table and then run a select * query???

I only need the ID field to sort them.

LVL 143

Accepted Solution

by:Guy Hengel [angelIII / a3]

Guy Hengel [angelIII / a3] earned 500 total points

ID: 337154922010-09-20

ok, this should do:

select column0, id
 from (
  select column0, id, row_number() over (partition by column0 order by id) rn
    from all_codes
 ) sq
where rn = 1
order by id

Open in new window

LVL 58

Expert Comment

by:cyberkiwi

ID: 337154932010-09-20

a3's query.
But I think this is what you mean

select column0
from (
select column0, id, row_number() over (partition by column0 order by id) rn
from all_codes
) sq
where rn = 1
order by id

LVL 2

Expert Comment

by:k_romych

ID: 337155412010-09-20

SELECT Column0, ID FROM all_codes
GROUP by Column0
ORDER BY Column0

LVL 2

Expert Comment

by:vsosu

ID: 337155642010-09-20

Here you go:

SELECT Column0
FROM all_codes
GROUP by Column0
ORDER BY min(val)

Author Closing Comment

by:TrentSlater

ID: 337156402010-09-20

Thanks AngelIII - version 3 worked like a charm. Thanks to all for the quick response.

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Step by step guide to take SQL Server 2012 new feature Available Groups (Hadr) to Production

Article by: Duane

Why is this different from all of the other step by step guides? Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…