1

I have two tables as shown below :

            Table Name : scores
+-------+-----------+--------------+-----------+
| UNID  |   name    |    score     |   PRID    |
+-------+-----------+--------------+-----------+
|  1    |  name_1   |      93      |        1  |
|  2    |  name_2   |      71      |        1  |
|  3    |  name_3   |      53      |        2  |
|  4    |  name_4   |      82      |        2  |
|  5    |  name_5   |      31      |        2  |
|  6    |  name_6   |      68      |        3  |
+-------+-----------+--------------+-----------+

And

                  Table Name : user_dir
+-------+-----------+--------------+-----------+
| PRID  |   fname   |    city      |   gender  |
+-------+-----------+--------------+-----------+
|  1    |  fname1   |      XX      |        m  |
|  2    |  fname2   |      YY      |        f  |
|  3    |  fname3   |      ZZ      |        f  |
+-------+-----------+--------------+-----------+

For any given PRID from user_dir, how can I get the entire row that corresponds to the highest value of score for that PRID?

As an example, for the user "fname2" with a PRID of 2, I want to pull this entire row (and not just the value of score) as follows :

 +-------+-----------+--------------+-----------+
 | UNID  |   name    |    score     |   PRID    |
 +-------+-----------+--------------+-----------+
 |  4    |  name_4   |      82      |        2  |

I tried this using the "max" function of that row, but it instead gives me the first row found with that PRID, along with the correct (max) value of score, as follows :

 +-------+-----------+--------------+-----------+
 | UNID  |   name    |    score     |   PRID    |
 +-------+-----------+--------------+-----------+
 |  3    |  name_3   |      82      |        2  |

EDIT : I misunderstood my requirement, the final result needed to have the corresponding "name" value for the highest score, not the highest score itself.

So the actual final result would be :

+-------+-----------+--------------+-----------+
| PRID  |   name    |    fname     |   score   |
+-------+-----------+--------------+-----------+
|  1    |  fname1   |    name_1    |     93    |
|  2    |  fname2   |    name_4    |     82    |
|  3    |  fname3   |    name_6    |     68    |
Sainath Krishnan
  • 2,089
  • 7
  • 28
  • 43
  • This question has been answered hundreds of times on Stack Overflow. I picked one of the oldest, highest-voted answers and voted to close this as a duplicate. If you don't like that one, click the tag I added and find one of the many other answers. – Bill Karwin Nov 29 '16 at 07:20
  • @BillKarwin The duplicate you linked to is for Oracle. It won't work in MySQL. – Barmar Nov 29 '16 at 07:24
  • See also http://dev.mysql.com/doc/refman/5.7/en/example-maximum-column-group-row.html – Bill Karwin Nov 29 '16 at 07:36

1 Answers1

1
SELECT s.*
FROM scores s
WHERE PRID = 2 AND score = (SELECT MAX(score) FROM scores WHERE PRID = 2)

If you wanted to obtain records containing all highest scores for each PRID, then you can try the following:

SELECT s.*
FROM scores s
WHERE score = (SELECT MAX(score) FROM scores s1 WHERE s.PRID = s1.PRID)
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360