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:
x
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;