Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.
COURSE of the MONTH
7 days, 22 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Need help with MS SQL Select Distinct Query - multiple columns
Posted on 2010-09-20
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!
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!
[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
Active 2 days ago
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
That works fine to remove the duplicates but it is sorting by ID, not column0. I need it sorted by column0
Active 2 days ago
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
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.
Could we copy these into a new table and then run a select * query???
I only need the ID field to sort them.
Active 2 days ago
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
Active today
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
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
Featured Post
At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL).
Depending on business requirements, a single Stored Procedure can return differe…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses
Course of the Month7 days, 22 hours left to enroll
765 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.