Using DISTINCT in a query

Question

Post reply

Using DISTINCT in a query

Paul Kendall

SSC Rookie

Points: 35
More actions
October 4, 2004 at 8:57 am

#89452

Hello,
I'm a SQL beginner, so I'll beg your forgiveness.
I have a multiple column table which I am trying to query and which has duplicate records in it. I would like to return unique records (using something like the DISTINCT operator, I would imagine) for several columns at the same time.
To elaborate somewhat, lets say I have 3 columns (A, B, C), each of which contain duplicate values. I am trying to return every DISTINCT(A) with its corresponding B and C value. If I use the following:
select DISTINCT(A), B, C, from tableABC
I don't get the number of unique records I would expect for A, I get everything repeated for each instance of A. Can someone tell me what additional condition I need to include to return every DISTINCT(A) along with its only instance of B and C?
Thanks in advance!
-Paul

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 1

select distinct a,b,c

from tableABC

-- order by a,b,c

returns the distinct combination of a,b,c in your table.This way "distinct" is applied at row level.

sometimes, one might want to count the distinct values of a column, this can be done with

select count(distinct a) as distinct_a_values from tableABC

hope this helps.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 2

You could use a group by clause to do this.

select a,b,c from tableABC group by a,b,c

If you have some sample data and the results that you are trying to achieve, that might help us come up with an idea.

Kathi Kellenberger

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

hildevan Say Hey Kid Points: 691 More actions · Answer 3

Hi

All suggestions works fine.

But we need know realy what we need.

For each case we have many solutions, but the best solutions is a least expensive.

Distinct clause is leats expensive than group by, but depends how many records was processed.

Hildevan O Bezerra

dongadoy SSC Eights! Points: 880 More actions · Answer 4

It seems to me that Paul is not talking about duplicate rows that could be solved with DISTINCT or GROUP BY. Paul is talking about multiple rows with the same value in column A but different values in columns B and C.

You want only one row for each value of column A. The difficulty is that SQL Server doesn't know which value of column B/C to use for that row.

Simplest way to handle the situation is to use min() or max() aggregate functions:

SELECT A, min(B), min(C) FROM tableABC GROUP BY A

Unfortunately, that's usually not very useful information.

You really need to define what you want for the B and C columns if there are multiple rows with the same value for column A.

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 5

or

SELECT A, count(distinct B) as B_Count, count(distinct C) as C_count

FROM tableABC

GROUP BY A