America America - 1 year ago 69
SQL Question

Select top record by group without order by

My source table looks like this

cust_id cust_name
1 John Smith
1 John K Smith
2 Mary B Snow
2 Mary Snow

and I would like to return

cust_id cust_name
1 John Smith
2 Mary B Snow

where one cust_name value is not preferred over another; I just want one row per cust_id, with an arbitrary cust_name attached.

I'm using

select cust_id, cust_name from customers
qualify 1 = row_number() over (partition by cust_id order by cust_name desc)

but this has the weakness that I can't use it in a subquery due to the
order by

Is there a way I can do this without the (conceptually unnecessary) ordering?

Answer Source
select cust_id, min(cust_name) 
from customers 
group by cust_id 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download