How to get a single row where multiple rows share the same column value

Question

Say I have an sql query that returns the following data:

USERID, LOCATIONID, SITE
usera, locationa, site1
userb, locationb, site1
userx, locationx, site1
userc, locationc, site2

I only care about unique SITE, so I would like the query to return:

USERID, LOCATIONID, SITE
usera, locationa, site1
userc, locationc, site2

How can I modify:

select userid, locationid, site from mytable order by site;

To accomplish this?

score 1 · Accepted Answer · answered Jun 27 '23 at 17:38

You're after a frequently asked solution for a top n rows per group

You can use a windowed row_number to assign a sequence to each group, here determined by the LocationId value:

with sites as (
  select userid, locationid, site,
    Row_Number() over(partition by site order by LocationId) rn
  from mytable
)
select userid, locationid, site
from sites
where rn = 1;

How to get a single row where multiple rows share the same column value

1 Answers1