Select Rows with maximum column value grouped by another column without nested select statement

Question

I know that this is a duplicate of Select Rows with Maximum Column Value group by Another Column but I want to select rows that have the maximum column value,as group by another column , but without nested select statement, I know it can be done like this:

SELECT
    T.Name,
    T.Rank,
    T.ID
FROM MyTable T
WHERE T.Rank = (
    SELECT MAX( T1.Rank) FROM MyTable T1
    WHERE T1.Name= T.Name
)

where ID, Rank, Name is the table schema, and I want to group by results by Name first, and then choose one row from each Name group, depending on which one has the highest Rank.

Attached is a sample of the table I want to select from enter image description here

Could you provide some sample data and a desired output? The `HAVING` clause could be what you are looking for: http://dev.mysql.com/doc/refman/5.1/en/select.html — Jens, Nov 21 '12 at 11:05
how to use the HAVING statement to state that I want those items having the max rank? — Hassan Mokdad, Nov 21 '12 at 11:12
@DrewPierce yes, only one output per name, having the highest rank among items with the same name — Hassan Mokdad, Nov 21 '12 at 11:13
then just do a select name,max(rank) from mytable group by name order by name — Drew, Nov 21 '12 at 11:14
@DrewPierce: You would be mixing the rank for different names. — Jens, Nov 21 '12 at 11:15
@DrewPierce your are right, but how can I include the ID in the select statement ? — Hassan Mokdad, Nov 21 '12 at 11:24

Drew · Accepted Answer · 2012-11-21T11:48:44.840

2

mysql> SELECT t1.nm, t1.rank,t1.id
 FROM mytable t1
 LEFT JOIN (
   SELECT nm, max(rank) as top
   FROM mytable t2
   GROUP BY nm
 ) AS t2 ON t1.nm=t2.nm AND t1.rank = t2.top
 WHERE t2.nm IS not NULL
 ORDER BY nm;

+----+------+---------+
| nm | rank | id      |
+----+------+---------+
| m  |   -1 | b7kjhsf |
| n  |   13 | d3sf    |
+----+------+---------+
2 rows in set (0.00 sec)

mysql> select * from mytable;

+----+------+----------+
| nm | rank | id       |
+----+------+----------+
| n  |   11 | asfd     |
| n  |   11 | bsf      |
| n  |   11 | zzasdfsf |
| n  |   13 | d3sf     |
| n  |   11 | effesf   |
| n  |   10 | yxxgesf  |
| n  |   11 | bkhjusf  |
| m  |   -1 | b7kjhsf  |
| m  |   -4 | cdfgabsf |
+----+------+----------+
9 rows in set (0.00 sec)

edited Nov 21 '12 at 11:48

answered Nov 21 '12 at 11:18

Drew

24,851
10
43
78

this seems to do the work, but how can I include the ID in the select statement ? – Hassan Mokdad Nov 21 '12 at 11:25
@HassanMokdad sorry fixed the above didnt know u wanted to go after id – Drew Nov 21 '12 at 11:49

score 0 · Answer 2 · answered Nov 21 '12 at 11:14

As mentioned in the other answer, the only other alternative that I know of, is using Common Table Expressions:

;WITH CTE AS
(
    T.Name,
    T.Rank,
    T.ID,
    ROW_NUMBER() OVER
       (PARTITION BY Name ORDER BY Rank DESC)
    AS RowNumber
    FROM MyTable
)

SELECT *
FROM CTE
WHERE RowNumber = 1

score 0 · Answer 3 · answered Nov 21 '12 at 11:17

0

SELECT Name, Id, Rank FROM 
(
    SELECT T.Name, T.Id, T.Rank, RANK() OVER (PARTITION BY T.Name ORDER BY T.Rank DESC) = 1 AS NameRank
    FROM MyTable T
)
WHERE NameRank = 1

answered Nov 21 '12 at 11:17

StevieG

8,639
23
31

score 0 · Answer 4 · answered Nov 21 '12 at 11:20

Not sure whether you are just trying to exclude the nested select, and whether joining aginst a subselect would be acceptable. If so:-

SELECT
    T.Name,
    T.Rank,
    T.ID
FROM MyTable T
INNER JOIN (SELECT Name, MAX(Rank) AS MaxRank FROM MyTable GROUP BY Name ) T1
ON T.Name = T1.Name
AND T.Rank = T1.MaxRank

Select Rows with maximum column value grouped by another column without nested select statement

4 Answers4