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_at
where each row has the oldestlistened_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;