Solved

Need help with MS SQL Select Distinct Query - multiple columns

Posted on 2010-09-20
10
441 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!
0
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
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33715394
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

0
 

Author Comment

by:TrentSlater
ID: 33715416
That works fine to remove the duplicates but it is sorting by ID, not column0. I need it sorted by column0
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33715419
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

0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 2

Expert Comment

by:k_romych
ID: 33715431
SELECT Column0, ID FROM all_codes
GROUP by Column0
ORDER BY id
0
 

Author Comment

by:TrentSlater
ID: 33715432
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.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 33715492
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

0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33715493
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
0
 
LVL 2

Expert Comment

by:k_romych
ID: 33715541
SELECT Column0, ID FROM all_codes
GROUP by Column0
ORDER BY Column0
0
 
LVL 2

Expert Comment

by:vsosu
ID: 33715564
Here you go:

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

Author Closing Comment

by:TrentSlater
ID: 33715640
Thanks AngelIII - version 3 worked like a charm. Thanks to all for the quick response.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query help 15 54
Adding SQL Server Browser in after install is complete 8 33
Options for Linking SQL tables to Access 2013 9 42
SQL Syntax 6 32
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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question