CODEWITHSUNDEEP
sqlsql-server-2012greatest-n-per-group
szpic
szpic

Reputation: 4496

Select record with highest value from each group

Assuming that I have tables like:

CustomerAdresses table:

Shop        Name        Surname     Adress
----------------------------------------------
SHOP1       name1       surname1    adress1_1 
SHOP2       name1       surname1    adress1_2 
SHOP1       name2       surname2    adress2_1 
SHOP3       name2       surname2    adress2_3 
SHOP3       name1       surname1    adress1_3

and

ShopPriority table:

Shop   Priority
---------------
SHOP1       1
SHOP2       2
SHOP3       3

I am trying to select customer adress with highest priority ( regardles the it is shop)

I wrote sql:

select ca.shop, ca.name, ca.surname, ca.adress, sp.priority
from CustomerAdresses ca
join ShopPriority sp on sp.shop = ca.shop
group by ca.name, ca.surname, ca.adress, ca.shop, sp.priority

this query returns me results like below:

Shop        name        surname     adress    priority
-------------------------------------------------------
SHOP1       name1       surname1    adress1_1   1
SHOP2       name1       surname1    adress1_2   2
SHOP3       name1       surname1    adress1_3   3
SHOP1       name2       surname2    adress2_1   1
SHOP3       name2       surname2    adress2_3   3

Now i am close to the think i need. records are grouped by the name and surname. Now I just need to get one record from each group with highest priority. I tried ordering record after grouping but then my whole grouping was falling apart

Upvotes: 0

Views: 113

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use row_number() function :

select t.*
from (select ca.shop, ca.name, ca.surname, ca.adress, sp.priority,
             row_number() over (partition by ca.shop order by sp.priority desc) as seq
      from CustomerAdresses ca join 
           ShopPriority sp 
           on sp.shop = ca.shop
      ) t
where seq = 1;

However, top (1) with ties also useful :

select top (1) with ties ca.shop, ca.name, ca.surname, ca.adress, sp.priority
from CustomerAdresses ca join 
     ShopPriority sp 
     on sp.shop = ca.shop
order by row_number() over (partition by ca.shop order by sp.priority desc);

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269483

Use the ANSI-standard row_number() function:

select spc.*
from (select ca.shop, ca.name, ca.surname, ca.adress, sp.priority,
             row_number() over (partition by ca.shop order by sp.priority desc) as seqnum
      from CustomerAdresses ca join
           ShopPriority sp
           on sp.shop = ca.shop
     ) spc
where seqnum = 1;

Upvotes: 1

Related Questions