How to select rows from MySQL based on max value of a one column and grouping two other columns?

Question

How can I select rows from MySQL based on max value of a one column and grouping two other columns? The query needs to group by code, group by element_id with where clause (element_id=1.B.1.b) and max(data_value).

code        element_id  data_value
11-1011.00  1.B.1.a     1.33        
11-1011.00  1.B.1.b     2.00    
11-1012.00  1.B.1.a     2.67    
11-1012.00  1.B.1.b     3.67    
11-1012.00  1.B.1.C     7.00

I have tried this:

SELECT * FROM (select
  max(`data_value`) as maxID, `code`, `element_id`
from
  table_name
GROUP BY `code`, `element_id`
ORDER BY maxID desc) abc
GROUP BY `code`

in that table i have lots of data. its a sample.

Here you can see more clear:

I need result :

11-1011.00 1.B.1.b 2.00

11-1012.00 1.B.1.c 7.00

this result for without where clasuse.

if using where clause i want only one result that match with element_id(1.B.1.b) that i have:

11-1011.00 1.B.1.b 2.00

possible duplicate of [SQL Select only rows with Max Value on a Column](http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) — vhu, Sep 18 '15 at 05:30

ankitr · Accepted Answer · 2015-09-18T19:40:20.637

You can achieve this by

SELECT a.code, max( a.maxID ) , a.element_id
FROM (
    SELECT *
    FROM (
        SELECT max(data_value) AS maxID, code , element_id
        FROM table_name
        GROUP BY code , element_id
        ORDER BY maxID DESC
    ) AS abc
    GROUP BY code
) AS a
WHERE a.element_id = '1.B.1.b'
GROUP BY a.code
ORDER BY a.code

or try this

SELECT a.code, a.element_id, a.data_value
FROM interests a
INNER JOIN (
    SELECT code, max( data_value ) data_value
    FROM table_name
    GROUP BY code
)b ON a.code = b.code
AND a.data_value = b.data_value
WHERE a.element_id = '1.B.1.b'
ORDER BY a.code

Uyghur Lives Matter · Answer 2 · 2015-09-19T13:25:28.327

So, you want to group by code and element_id, and select the max data_value. This can be achieved by.

SELECT
    code,
    element_id,
    max(data_value) AS data_value
FROM table_name
GROUP BY
    code,
    element_id

Now, if there are a lot more columns within table_name, you'll want to make the previous a query a sub-query, join to table_name, and then select your desired columns.

SELECT table_name.*
FROM (
    SELECT
        code,
        element_id,
        max(data_value) AS data_value
    FROM table_name
    GROUP BY
        code,
        element_id
) AS max_record
INNER JOIN table_name
    ON table_name.code = max_record.code
    AND table_name.element_id = max_record.element_id
    AND table_name.data_value = max_record.data_value

How to select rows from MySQL based on max value of a one column and grouping two other columns?

2 Answers2