Select record with highest value from each group

Question

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

What rdbms are you using?

Tim Schmelter
– Tim Schmelter

2018-07-06 14:51:10 +00:00
Commented Jul 6, 2018 at 14:51 — Tim Schmelter
– Tim Schmelter, Commented Jul 6, 2018 at 14:51
MS Sql server pro 2012

szpic
– szpic

2018-07-06 15:04:23 +00:00
Commented Jul 6, 2018 at 15:04 — szpic
– szpic, Commented Jul 6, 2018 at 15:04

Yogesh Sharma · Accepted Answer · 2018-07-06 15:37:18Z

1

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);

edited Jul 6, 2018 at 15:37

answered Jul 6, 2018 at 14:51

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2018-07-06 14:52:53Z

1

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;

answered Jul 6, 2018 at 14:52

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Select record with highest value from each group

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related