Experts Exchange connects you with the people and services you need so you can get back to work.

Need help with MS SQL Select Distinct Query - multiple columns

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!

TrentSlater

Asked: 2010-09-20

Advertise Here

1 Solution

LVL 143

Guy Hengel [angelIII / a3]Billing EngineerCommented: 2010-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

TrentSlaterAuthor Commented: 2010-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

Guy Hengel [angelIII / a3]Billing EngineerCommented: 2010-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

k_romychCommented: 2010-09-20

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

TrentSlaterAuthor Commented: 2010-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

Guy Hengel [angelIII / a3]Billing EngineerCommented: 2010-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

cyberkiwiCommented: 2010-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