SQL: Max ID Group By With Condition

Question

I'm a SQL newbie, here is my Table:

+----+--------+---------+------------+
| ID | Person | Success | Message    |
+----+--------+---------+------------+
|  1 | Alice  |       1 | Hello      |
+----+--------+---------+------------+
|  2 | Bob    |       0 | World      |
+----+--------+---------+------------+
|  3 | Alice  |       0 | Foo        |
+----+--------+---------+------------+
|  4 | Clark  |       1 | Bar        |
+----+--------+---------+------------+

And I want to select latest (MAX ID assuming ID is incremental) records which is success for each person

Expected result:

+----+--------+---------+------------+
| ID | Person | Success | Message    |
+----+--------+---------+------------+
|  4 | Clark  |       1 | Bar        |
+----+--------+---------+------------+
// Alice's latest record is not success, ignore
// Bob has no success record, ignore

Here is my current approach:

SELECT *
FROM test AS t1
RIGHT JOIN (
    SELECT MAX(id) AS max_id
    FROM test
    GROUP BY Person
) AS t2
ON t1.id = t2.max_id
WHERE t1.success = 1

// Select Max ID group by person, join with original table, then filter by success

And I'm wondering if there are better approach or neater SQL lines for this?

Thank you so much!

*I want to select latest records which is success for each person* The term "latest" need in ORDER BY expression to be defined. And this expression must provide rows uniqueness. Does `ORDER BY id` is such expression for your task? — Akina, Sep 02 '21 at 05:43
try to avoid using id for determining newest/oldest; ideally id should treated as an opaque value, with no extra meaning than identifying a record. if you need times or order you should track those in a separate field — ysth, Sep 02 '21 at 05:58
Related: [Get records with max value for each group of grouped SQL results](https://stackoverflow.com/q/12102200/2943403) — mickmackusa, Apr 30 '22 at 17:03

score 0 · Answer 1 · answered Sep 02 '21 at 05:43

Use ROW_NUMBER to filter down to the latest record for each person. Then, subquery and retain only success records:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Person ORDER BY ID DESC) rn
    FROM test
)

SELECT ID, Person, Success, Message
FROM cte
WHERE Success = 1;

For an old school way of doing this, if perhaps you are still using MySQL 5.7, we can try:

SELECT t1.ID, t1.Person, t1.Success, t1.Message
FROM test t1
INNER JOIN
(
    SELECT Person, MAX(ID) AS MAX_ID
    FROM test
    GROUP BY Person
) t2
    ON t2.Person = t1.Person AND
       t2.MAX_ID = t1.ID
WHERE
    t1.Success = 1;

score 0 · Accepted Answer · answered Sep 02 '21 at 05:44

0

SELECT *
FROM test t1
WHERE Success 
  AND NOT EXISTS ( SELECT NULL
                   FROM test t2
                   WHERE t2.id > t1.id
                     AND t1.person = t2.person
                     AND NOT Success );

SELECT t1.*
FROM test t1
LEFT JOIN t2 ON t1.person = t2.person
            AND t2.id > t1.id
            AND NOT t2.Success
WHERE t1.Success
  AND t2.id IS NULL;

answered Sep 02 '21 at 05:44

Akina

39,301
5
14
25

This answer is missing its educational explanation. How do these queries work? Why did you make these choices? Which style might perform better in different circumstances? These are things that improve the Researcher eXperience. – mickmackusa Apr 30 '22 at 17:04

score 0 · Answer 3 · answered Sep 02 '21 at 06:15

0

You could use:

select * from test t1
where exists (
  select 1 from test
  where success 
  having max(id) = t1.id
)

Sql fiddle

Alternative query:

select * from test t1
where id = (
  select max(id) from test
  where success 
)

Sql fiddle

answered Sep 02 '21 at 06:15

Michał Turczyn

32,028
14
47
69

This answer is missing its educational explanation. How do these queries work? Why did you make these choices? Which style might perform better in different circumstances? These are things that improve the Researcher eXperience. – mickmackusa Apr 30 '22 at 17:06

score 0 · Answer 4 · answered Sep 02 '21 at 11:26

And I want to select latest (MAX ID assuming ID is incremental) records which is success for each person

One way to do this uses a correlated subquery like this:

select t.*
from test t
where t.status = 'success' and
      t.id = (select max(t2.id) from test t2 where t2.person = t.person);

This is pretty much a direct translation of what you are asking. The condition on id is the "latest record" for each person. The status = 'success' is the filter you want.

SQL: Max ID Group By With Condition

4 Answers4