Let's say:
I want to query colA, colB and colC in my table.
I want to see DISTINCT values but I don't want colA to be a criteria for distinction.
Omitting colA isn't an option.
What's the best way to structure that query?
views:
251answers:
3
A:
Perhaps this site will help? It has some detailed instructions and it appears to be what you're trying to do.
http://oscarvalles.wordpress.com/2008/01/28/sql-distinct-on-one-column-only/
Nitrodist
2010-05-26 23:37:29
A:
I dont know the syntax for a temporary table, so pseudocode if you please =)
Select Distinct ColB, ColC into @Temp
From SomeTable
Where (predicates)
Select ColA, ColB, ColC
From SomeTable
Inner Join @Temp on (SomeTable.ColB = @Temp.ColB and SomeTable.ColC = @Temp.ColC)
Where (predicates) /* Added for comments */
Hope this helps.
Fanged
2010-05-26 23:38:33
Wouldn't that just produce a copy of SomeTable? The inner join will match exactly one row in @Temp.
Keith Randall
2010-05-26 23:42:21
The Inner Join will match all rows that contain both column values, and no others.
Fanged
2010-05-26 23:52:52
Yes, but all rows in SomeTable (matching the predicate, if there is one) have their (ColB, ColC) pairs in the @Temp table.
Keith Randall
2010-05-27 06:09:09
Ahh, I see. You mean when a paticular ColA value might not be wanted?Will add a predicate to second select.
Fanged
2010-05-28 00:39:08
No, that's not what I mean. I mean even without predicates in both statements, you aren't doing what the OP wanted. The inner join with @Temp is pointless - you are finding the matching entry in @Temp for each entry in SomeTable. There will always be exactly one such row. As such, your join is not accomplishing anything - you are just adding some extra redundant columns to SomeTable and then throwing them away.
Keith Randall
2010-05-28 02:37:51
A:
There are two cases here. Let's say you have the data
A B C (columns)
a b c1
a b c2
Taking distinct values of A, B gives just one result (a,b), with two values for column C. So the question is do you want to see all values of C or just one value for each distinct value of columns A and B?
If you want to see just one value of C, then you can write
SELECT A, B, MAX(C) FROM YourTable
GROUP BY A, B
On the other hand, if you want to see all values for C then
SELECT DISTINCT A, B, C FROM YourTable WHERE ROW(A,B) IN
(SELECT A, B FROM YourTable
GROUP BY A, B)
gives you that. This last alternative is needed if there are other columns in the table.
mdma
2010-05-26 23:45:14
The first option looks good, but isn't the second one equivalent to SELECT DISTINCT A,B,C ?
Keith Randall
2010-05-27 06:13:33
@Keith - If there are no other columns, then it is the same, but as the answer says "This last alternative is needed if there are other columns in the table."
mdma
2010-05-27 07:47:55
I don't understand how SELECT DISTINCT A,B,C doesn't work if there are extra columns.
Keith Randall
2010-05-27 16:04:31