Select but not quite distinct =)
Select but not quite distinct =)
(OP)
Hi!
I have a problem figuring out how to formulate my select-query. I will try to illustrate my problem :)
Table:
col1 | col2
-----------
A | 10
B | 13
B | 14
C | 11
I want to select every distinct occurence in col1 choosing the record with the highest value in col2, ie. i want the result set to be:
col1 | col2
-----------
A | 10
B | 14
C | 11
Any clues as how to do this? Any help wold be appreciated!
Regards,
Jonas
I have a problem figuring out how to formulate my select-query. I will try to illustrate my problem :)
Table:
col1 | col2
-----------
A | 10
B | 13
B | 14
C | 11
I want to select every distinct occurence in col1 choosing the record with the highest value in col2, ie. i want the result set to be:
col1 | col2
-----------
A | 10
B | 14
C | 11
Any clues as how to do this? Any help wold be appreciated!
Regards,
Jonas
Talk To Other Members
RE: Select but not quite distinct =)
Table:
col1 | col2 | col3
--------------------
A | 10 | Z
B | 13 | X
B | 14 | X
C | 11 | Y
Now, if I use
SELECT DISTINCT col1, MAX(col2), col3 FROM Table GROUP BY col1, col3
I still get all 4 posts even though I only wanted 3, ie
col1 | col2 | col3
--------------------
A | 10 | Z
B | 14 | X
C | 11 | Y
Why do I have to GROUP BY col3 as well?
How do I include col3 without corrupting my previously successful query?
Rgr,
Jonas
RE: Select but not quite distinct =)
In your example, it appears that col3 is a constant function or mapping of col1, such that A=Z, B=X, C=Y. If that is the case, then you can exclude col3 from the query and then calculate it after you get the query's results.
Hope this helps!