Catch value of col1 based on grouped value of col2

Question

This is the general structure of my table

USERID | QUANTITY | FRIEND_ID |         DATE
---------------------------------------------------
     1 |        3 |         7 | 2019-06-22 10:00:00
     2 |        2 |         8 | 2019-06-22 11:00:00
     1 |        2 |         5 | 2019-06-22 12:00:00

Then I want to group these data like this

SELECT USERID,
       SUM(QUANTITY) AS COUNT,
       // I NEED THE FRIEND_ID OF THE RECORD WITH MAX(DATE)
       MAX(DATE)
FROM THISTABLE
GROUP BY USERID

How can I select the value of a column based on the grouped value of another column?

What version of MySQL are you using? – Shawn Jun 27 '19 at 16:18 — Shawn, Jun 27 '19 at 16:18

score 0 · Accepted Answer · answered Jun 27 '19 at 16:22

You can join your query to the table to get the FRIEND_ID:

SELECT g.USERID, g.COUNT, t.FRIEND_ID, g.MAXDATE
FROM (
  SELECT USERID, SUM(QUANTITY) AS COUNT, MAX(DATE) AS MAXDATE
  FROM THISTABLE
  GROUP BY USERID
) g INNER JOIN THISTABLE t
on t.USERID = g.USERID AND t.DATE = g.MAXDATE

Catch value of col1 based on grouped value of col2

1 Answers1