Find MAX from field1, corresponding field2, GROUP BY field3

Question

I have data like:

id, id2, datetime_created
1, 1, 9/10
1, 2, 9/9
1, 3, 9/8
2, 4, 9/8
2, 5, 9/2
2, 6, 9/1
3, 7, 9/3
3, 8, 9/4
3, 9, 9/5

Expected output:

id, id2
1, 1
2, 4
3, 9

I've tried a number of things, including:

SELECT id, id2, MAX(datetime_created)
FROM table
GROUP BY id2

,

SELECT id, id2, MAX(datetime_created)
FROM table
GROUP BY id, id2

,

SELECT id, id2
FROM table
INNER JOIN (
    SELECT MAX(datetime_created), id
    FROM table)

This question was not sufficient. The kicker is the GROUP BY a different field than one of the ones I need without GROUP BYing the second field, as this returns too many rows (from my sample dataset, it would return all rows).

In the expected result, there is no mention of datetime_created. Do you really mean "GROUP BY field3" and not "ignore field 3", that has no bearing on the result as far as the sample data is concerned? — Félix Adriyel Gagnon-Grenier, Sep 19 '18 at 19:35
I don't need the `datetime_created`, but I need the `id` and `id2` `GROUP BY id` with the most recent `datetime_created` for that `id`. Several of the answers I've received understood the question. — ChootsMagoots, Sep 20 '18 at 08:57

score 2 · Accepted Answer · edited Sep 19 '18 at 19:38

2

Use row_number() over() instead of group by:

SELECT
    id, id2, datetime_created
FROM (
    SELECT
        id, id2, datetime_created
      , ROW_NUMBER() OVER (PARTITION BY id ORDER BY datetime_created DESC) AS rn
    FROM table1
) d
WHERE rn = 1

This will give you access to the whole rows that have the maximum datetime value

Reference: ROW_NUMBER Window Function

edited Sep 19 '18 at 19:38

Peter Mortensen

30,738
21
105
131

answered Sep 19 '18 at 03:13

Paul Maxwell

33,002
3
32
51

1

@ChootsMagoots using UPPERCASE for SQL is a preference, not a requirement – Paul Maxwell Sep 19 '18 at 03:27
Yeah, but my boss and my coworker both don't use uppercase, and I find it MUCH easier to read... I don't feel comfortable asking them to change how they do things, but I do feel comfortable requesting it here, particularly because I can do the work. Small victories :P – ChootsMagoots Sep 19 '18 at 03:31

score 1 · Answer 2 · edited Sep 19 '18 at 19:39

1

You might try the following one:

SELECT id, id2
  FROM table
 WHERE (id, datetime_created) in
      (
        SELECT id, MAX(datetime_created)
          FROM table
         GROUP BY id
       )
ORDER BY id;

Rextester Demo [P.S. even if in Oracle the same logic works]

edited Sep 19 '18 at 19:39

Peter Mortensen

30,738
21
105
131

answered Sep 19 '18 at 03:34

Barbaros Özhan

59,113
10
31
55

score 0 · Answer 3 · edited Sep 19 '18 at 19:39

0

This could help you:

SELECT tt.id,tt.id2 FROM table tt LEFT JOIN (SELECT id, MAX(datetime_created) AS cr
          FROM table 
         GROUP BY id)q ON q.id=tt.id AND q.cr=tt.datetime_created WHERE q.id IS NOT NULL;

edited Sep 19 '18 at 19:39

Peter Mortensen

30,738
21
105
131

answered Sep 19 '18 at 06:42

Berkay Kaan

295
3
10

Find MAX from field1, corresponding field2, GROUP BY field3

3 Answers3