Skip to content
Advertisement

Selecting different parameters for a group by statement in mysql?

I have a dataset like this:

enter image description here

Here in this dataset, there is No NULL value for all these 3 columns, also user_name and recording_msid pair can repeat but each repeated pair will have a distinct timestamp in listened_at.

Note for distinct pairs of (user_name, recording_msid) the listened_at can repeat.

I am trying to group by the table using:

SELECT
  user_name, recording_msid, COUNT(*)
FROM
  `listenbrainz.listenbrainz.listen`
GROUP BY
  user_name, recording_msid;

and get results like:

enter image description here

But what I also want in this result is another column of listened_at where each row has the oldest listened_at value for every repeating pair of (user_name, recording_msid) i.e. every row in the below table.

Advertisement

Answer

But what I also want in this result is another column of listened_at where each row has the oldest listened_at value for every repeating pair of (user_name, recording_msid)

Use MIN():

SELECT
    user_name, 
    recording_msid, 
    COUNT(*) no_records, 
    MIN(listened_at) min_listened_at
FROM `listenbrainz.listenbrainz.listen`
GROUP BY user_name, recording_msid;
User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement