I have a dataset like this:
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:
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_atwhere each row has the oldestlistened_atvalue 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;

