SQL: Select all distinct and pick the row that has the max value in another column

Asked Dec 28 '13 at 08:23

Active Dec 28 '13 at 09:48

Viewed 67 times

| nid | vid | max_score |
|-----+-----+-----------|
|  1  |  1  |     1     |
|-----+-----+-----------|
|  1  |  2  |     1     |
|-----+-----+-----------|
|  2  |  1  |     1     |
|-----+-----+-----------|
|  2  |  2  |     2     |
-------------------------

I would like to select only the distinct nid, but also only take the row where the vid is the highest.

So the result here would give me

1, 2, 1

2, 2, 2

edited Dec 28 '13 at 08:25

Strawberry

33,750
13
40
57

asked Dec 28 '13 at 08:23

Bobby S

4,006
9
42
61

Luckily, this question is asked (and answered) almost every single day in SO. – Strawberry Dec 28 '13 at 08:25
1

`SELECT nid, MAX(vid) vid,max_score FROM table GROUP BY nid` – M Khalid Junaid Dec 28 '13 at 08:26
@MKhalidJunaid No. Because, presumably, BobbyS also wants the corresponding max_score – Strawberry Dec 28 '13 at 08:27
@ChrisHayes. The SQL tag itself explains why. – Strawberry Dec 28 '13 at 08:46
@BobbyS All answers provided so far are wrong - which is depressing. Do we really have to go over this one again? – Strawberry Dec 28 '13 at 08:51
Yes, the inclusion of the term 'exclusively' is silly. Anyone asking such a question is unlikely to know (or care) whether or not the solution relies upon a non-standard extension! Maybe I'll edit it... – Strawberry Dec 28 '13 at 09:06
I agree with your logic, but not your conclusion, because in practice answers for one RDBMS are rarely applicable to another. – Strawberry Dec 28 '13 at 10:35

SQL: Select all distinct and pick the row that has the max value in another column

0 Answers0