TSQL Finding maximum values in multiple rows

Question

In my query I need to find the supplier with the highest costs for every single year.

SELECT      YEAR(ORDERS.OrderDate),
            MAX(ORDERS.Freight) AS [Greatest cost]
FROM        ORDERS
GROUP BY    YEAR(ORDERS.OrderDate)
ORDER BY    YEAR(ORDERS.OrderDate) ASC

This code does give me the maximum cost per year, it doesn't give me the name of the supplier.

SELECT      YEAR(ORDERS.OrderDate),
            SHIPPERS.ShipperID,
            SHIPPERS.CompanyName,
            MAX(ORDERS.Freight) AS [Greatest cost]
FROM        ORDERS, SHIPPERS
WHERE       SHIPPERS.ShipperID = ORDERS.ShipVia
GROUP BY    YEAR(ORDERS.OrderDate),
            SHIPPERS.ShipperID,
            SHIPPERS.CompanyName
ORDER BY    YEAR(ORDERS.OrderDate) ASC

This code then gave me too much, as in, it gave me all the suppliers (with their highest numbers) for every single year, while I need the highest supplier per year.

Thanks in advance!

Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI JOIN syntax in the ANSI-92 SQL Standard (more than 20 years ago) and its use is discouraged — marc_s
– marc_s, Commented Nov 15, 2015 at 8:54
If one supplier had one order of 20 and a second supplier had three orders of 10 each, which supplier do you want for that year? — MatBailie
– MatBailie, Commented Nov 15, 2015 at 8:58
Let's say there are two suppliers in two years. In 1901 Supplier 1 has 10 and Supplier 2 has 11. Then in 1902 Supplier 1 has 8 and Supplier 2 has 15. What I want my query to do is to show me the name of the supplier for each year with the highest value. — The Yaeger
– The Yaeger, Commented Nov 15, 2015 at 9:23

PaulF · Accepted Answer · 2015-11-15 01:38:42Z

1

There are likely several ways to do this. Here's one: http://sqlfiddle.com/#!6/47d38/3/0

Test data:

create table ORDERS
(
  OrderDate datetime,
  ShipVia int,
  Freight int
);

create table SHIPPERS
(
  ShipperID int,
  CompanyName nvarchar(100)
);

insert SHIPPERS values (1, 'Shipper1'), (2, 'Shipper2'), (3, 'Shipper3');

insert ORDERS values
('2011-2-1', 1, 10),
('2011-3-1', 1, 20),
('2011-2-2', 2, 5),
('2011-3-2', 2, 10),
('2011-2-3', 3, 18),
('2012-2-1', 1, 10),
('2012-3-1', 1, 20),
('2012-2-2', 2, 25),
('2012-3-2', 2, 40),
('2012-2-3', 3, 18);

Query:

with A as
(
  select
    YEAR(O.OrderDate) as [year],
    S.ShipperID,
    SUM(O.Freight) as [totalFreight]
  from ORDERS as O
  join SHIPPERS as S on O.ShipVia = S.ShipperId
  group by YEAR(O.OrderDate), S.ShipperId
)
select A.*, S.CompanyName
from A
join SHIPPERS as S on A.ShipperID = S.ShipperID
where A.totalFreight >=ALL
  (select totalFreight from A as Ainner where A.[year] = Ainner.[year]);

Results:

year    ShipperID    totalFreight    CompanyName
2011    1            30              Shipper1
2012    2            65              Shipper2

answered Nov 15, 2015 at 1:38

PaulF

1,1839 silver badges14 bronze badges

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

1 Comment

The Yaeger Over a year ago

This code works beautifully, but it seems like such a long way to get there. I had no idea it was this hard, I never could've found the answer myself. Thank you very much!

paparazzo · Accepted Answer · 2015-11-15 15:14:35Z

0

select * from 
(
SELECT      YEAR(ORDERS.OrderDate),
            SHIPPERS.ShipperID,
            SHIPPERS.CompanyName,
            MAX(ORDERS.Freight) over (partition by YEAR(ORDERS.OrderDate), SHIPPERS.ShipperID) as max,
            row_number() over (partition by YEAR(ORDERS.OrderDate), 
                               order by ORDERS.Freight desc) as rn

FROM        ORDERS, SHIPPERS
WHERE       SHIPPERS.ShipperID = ORDERS.ShipVia 
) tt
where tt.rn = 1

edited Nov 15, 2015 at 15:14

answered Nov 15, 2015 at 8:55

paparazzo

45.2k24 gold badges110 silver badges180 bronze badges

Collectives™ on Stack Overflow

TSQL Finding maximum values in multiple rows

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related