SQL: Fetch additional column without adding in GROUP BY

I’ve a query:

SELECT
    asset_ref_id,
    MAX(updated_at),
    people_id
FROM
    asset_aud
WHERE
    revtype = 2
    AND updated_at BETWEEN TO_DATE('2020-05-20T10:09:00', 'YYYY-MM-DD"T"HH24:MI:SS') AND TO_DATE('2020-05-24T10:18:00', 'YYYY-MM-DD"T"HH24:MI:SS'
    )
GROUP BY
    asset_ref_id,
    people_id

I also want to fetch id column from asset_aud table in the resultset. But I do not want to add it in groupby , because it will then add a group condition w.r.t to id column.

So how can fetch id column as well without inflating the resultset?

Answer

Use an aggregation column:

min(asset_aud.id)

or perhaps:

listagg(asset_aud.id, ',') within group (order by asset_aud.id)

Advertisement

Answer