Mysql, How to group a set of rows by using the max value of another column?

Question

I have a table that might have one or more rows with the same value for the key_property column.

I want to build a query in MySql that returns a set where every value of key_property is represented for only one of its corresponding rows and that it also receives a key_property filter for a like sentence, chosen by the max value of another column (say, event_id).

How can I achieve that?

UPDATE:

Here's a sample of the non-filtered table:

+--------------+--------+----------+
| key_property | others | event_id |
+--------------+--------+----------+
|    abcd      | B      |     1    |
|    abcd      | A      |     2    |
|    defg      | C      |     3    |
|    abcd      | D      |     4    |
|    hijk      | f      |     4    |
+--------------+--------+----------+

When executing the query with the filter setted as 'd', the result data should look like this:

+--------------+--------+----------+
| key_property | others | event_id |
+--------------+--------+----------+
|    abcd      | D      |     4    |
|    defg      | C      |     3    |
+--------------+--------+----------+

http://dev.mysql.com/doc/refman/5.7/en/example-maximum-column-group-row.html — CBroe, Apr 27 '16 at 19:48
have you read http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?rq=1 — codemonkeyliketab, Apr 27 '16 at 19:54
@codemonkeyliketab I have now, but it doesn't actually cover all my requirements. I edited the question to make it clearer. — Daniel Calderon Mori, Apr 27 '16 at 20:10
it seems to me that codemonkeyliketab's suggestion with an additional where condition `key_property like '%d%'` would work — ddirect, Apr 27 '16 at 20:26

score 0 · Accepted Answer · answered Apr 27 '16 at 20:29

0

SELECT * 
FROM tab WHERE (key_property,event_id) IN 
( SELECT key_property, MAX(event_id)
  FROM tab
  WHERE key_property like '%d%'
  GROUP BY key_property
)

answered Apr 27 '16 at 20:29

Reto

1,305
1
18
32

@Daniel Calderon Mori this should work as expected, doesn't it? – Reto Apr 27 '16 at 20:47

Mysql, How to group a set of rows by using the max value of another column?

1 Answers1