SQL: From a group by get another column on max date

Question

So I have a table more less like this name it tests_summary:

test_id	completed	remaining	score_date
200	6	1	2022-05-02
200	2	7	2022-05-01
200	5	10	2022-04-29
200	5	15	2022-04-28
199	10	0	2022-05-02
199	2	10	2022-05-01

First I tried to group by each test_id, SUM every completed by each test, but I want to get in the remaining column, the remaining value in the most recent score date. Example test_id 200, the most recent date is 2022-05-02, and 1 is remaining that is the value I want. In this table is already ordered but in my real table is not.
Expected result

test_id	completed	remaining
200	18	1
199	12	0

version 5.7.12

Does this answer your question? [Fetch the row which has the Max value for a column](https://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column) — philipxy, May 04 '22 at 18:19

score 0 · Answer 1 · answered May 02 '22 at 17:54

0

You can use row number function to give you a 1 for every item you care about.

Note, I use * here as a shorthand but you should only select the columns you are interested in, in your final code.

SELECT * 
FROM (
  SELECT z.*, ROW_NUMBER(PARTITION BY test_id ORDER BY score_date DESC) AS RN
  FROM sometableyoudidnotname z
) X
WHERE X.RN = 1

answered May 02 '22 at 17:54

Hogan

69,564
10
76
117

Not in my current mysql version RowNumber – Josué Arreola May 02 '22 at 20:02
what sql are you using @JosuéArreola? – Hogan May 02 '22 at 20:05
row_number has been in mysql since version 8 -- about 4 years ago – Hogan May 02 '22 at 20:06
5.7.12 is my version – Josué Arreola May 04 '22 at 17:37
@JosuéArreola -- that was released in 2015 -- might want to consider an upgrade (5.x will be supported till oct 23) – Hogan May 04 '22 at 19:36

Barbaros Özhan · Answer 2 · 2022-05-02T18:02:01.283

0

You can use

SELECT test_id, completed, remaining
  FROM (SELECT test_id,
               remaining,
               SUM(completed) OVER(PARTITION BY test_id) AS completed,               
               ROW_NUMBER() OVER(PARTITION BY test_id ORDER BY score_date DESC) AS rn
          FROM t) tt
 WHERE rn = 1

where you can get the latest date through descendingly sorted by the score_date while partitioning by test_id in order to group by each of them provided that your database and its version is suitable to use a window(or analytic) function

edited May 02 '22 at 18:02

answered May 02 '22 at 17:55

Barbaros Özhan

59,113
10
31
55

ROW_NUMBER not in my version – Josué Arreola May 02 '22 at 20:03

Jesusbrother · Answer 3 · 2022-05-02T18:10:28.953

0

Upd. That solution for PostgreSQL only. Thanks Hogan for correcting me

I'm not sure that my solution will be the most optimal for performance, but nevertheless it works. The main idea is to use the DISTINCT ON construct on the "remaining" column and separately summarize the "completed" column with a subquery:

WITH
    t AS (SELECT 200                AS test_id,
                 0                  AS completed,
                 1                  AS remaining,
                 '2022-05-02'::DATE AS score_date
          UNION
          SELECT 200,
                 2,
                 3,
                 '2022-05-01'
          UNION
          SELECT 199,
                 10,
                 0,
                 '2022-05-02'
          UNION
          SELECT 199,
                 2,
                 10,
                 '2022-05-01')
SELECT
    distinct on (t1.test_id)
        t1.remaining,
        t1.test_id,
        (select sum(completed) from t where t.test_id = t1.test_id)
    FROM t t1
ORDER BY t1.test_id,t1.score_date desc;

output result

edited May 02 '22 at 18:10

answered May 02 '22 at 17:56

Jesusbrother

503
2
12

They only want the most recent date – Hogan May 02 '22 at 17:56
yup, if you add "t1.score_date" at the end of my select block, you'll get score_date = '2022-05-02' for both lines. Coz we use DISTINCT ON ... ORDER BY date DESC construction – Jesusbrother May 02 '22 at 17:59
oh right -- this is a progres only feature. Why di you think they are using that platform? – Hogan May 02 '22 at 18:00
Damn, that's my mistake, you're right. I'll add this point to my comment, maybe it will help someone with a similar question in PG. – Jesusbrother May 02 '22 at 18:08
Hey, thanks for replying. What if there are thousands of days for the same test_id ? – Josué Arreola May 02 '22 at 18:32
I updated the post, in order to give it more clarity on what I want to achieve. – Josué Arreola May 02 '22 at 18:42

SQL: From a group by get another column on max date

3 Answers3