CODEWITHSUNDEEP
sql
DJM
DJM

Reputation: 21

Min per group in SQL but with a caveat

I've got this table in SQL below and I need to return "the car vendors that will never be used if the car purchaser is a rational person" or "The vendor for which all car prices are more expensive then others". I've tried to do the idea of joining with itself but I am unable to get it work. The resulting output should be vendor 3 since its price for car 3 and 4 is more expensive than the other option. 

id  car_vendor_id   vendor_name car_id  price    
---------------------------------------------
1        1            Vendor 1    1     25000    
2        1            Vendor 1    2     40000
3        2            Vendor 2    2     35000
4        2            Vendor 2    3     25000
5        3            Vendor 3    3     28000
6        3            Vendor 3    4     40000
7        4            Vendor 4    4     35000
8        4            Vendor 4    5     20000
9        5            Vendor 5    5     18000
10       5            Vendor 5    6     32000
11       6            Vendor 6    6     30000
12       6            Vendor 6    7     20000

Upvotes: 2

Views: 99

Answers (3)

Andrei Odegov
Andrei Odegov

Reputation: 3429

Check on the next query:

declare @car table(Vendor int, Car int, Price int);
insert @car
  values
    (1,1,25000),(1,2,40000),(2,2,35000),(2,3,25000),
    (3,3,28000),(3,4,40000),(4,4,35000),(4,5,20000),
    (5,5,18000),(5,6,32000),(6,6,30000),(6,7,20000);

with
  a as (
    select
      vendor, price,
      count(*) over(partition by car) cq,
      count(*) over(partition by vendor) vcq,
      max(price) over(partition by car) xcp
    from @car
  )
select vendor
from a
where cq > 1 and xcp = price
group by vendor, vcq
having count(*) = vcq;

To try the query online, please click here.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269673

One method is row_number() and aggregation:

select car_vendor_id, vendor_name
from (select t.*,
             rank() over (partition by car_id order by price) as seqnum
      from t
     ) t
group by car_vendor_id, vendor_name
having min(seqnum) > 1;

The having clause is selecting rows where the vendor has no cars that are "first" based on price.

Upvotes: 1

TomC
TomC

Reputation: 2814

The following query uses a CTE to work out the price order for each car, so the most expensive is 1.

It then excludes rows where there is a row for the vendor where they are not the most expensive, and lastly checks they are are not the only vendor for a car.

declare @Car table(Vendor int, Car int, Price int)
insert @Car values (1,1,25000),(1,2,40000),(2,2,35000),(2,3,25000),(3,3,28000),(3,4,40000),(4,4,35000),(4,5,20000),(5,5,18000),(5,6,32000),(6,6,30000),(6,7,20000)
;with Price as (
    select *, row_number() over(partition by Car order by Price desc) as r from @Car Car
)
select * from Price
where not exists(select * from Price p2 where p2.Vendor=Price.Vendor and p2.r>1)
and Vendor not in (
select Vendor from @Car where Car in (select Car from @Car group by Car having count(*)=1)
)

Upvotes: 0

Related Questions