CODEWITHSUNDEEP
sqlsql-server
nathan
nathan

Reputation: 83

How to get multiple fields of a max value in Sql Server?

I have a table of boxes. The fields are the CartonGroup (which company this box belongs too), the size of the box (could be used as the name), and the dimensions. Here is what that table looks like:

+--------------+--------+-----------+-----------+-----------+------------+
| CARTON_GROUP |  Size  |  HEIGHT   |   WIDTH   |  LENGTH   | MAX_WEIGHT |
+--------------+--------+-----------+-----------+-----------+------------+
|        09001 | 900129 | 28.000000 | 20.000000 | 20.000000 | 40.000000  |
|        09001 | 900128 | 26.000000 | 20.000000 | 18.000000 | 40.000000  |
|        09001 | 900127 | 26.000000 | 20.000000 | 16.000000 | 40.000000  |
|        09001 | 900126 | 26.000000 | 20.000000 | 14.000000 | 40.000000  |
|        09001 | 900125 | 24.000000 | 18.000000 | 16.000000 | 40.000000  |
|        09001 |  90019 | 20.000000 | 16.500000 | 4.500000  | 40.000000  |
|        09001 |  90018 | 14.000000 | 8.000000  | 12.000000 | 40.000000  |
|        09001 |  90017 | 23.000000 | 18.500000 | 3.000000  | 40.000000  |
|        09001 |  90016 | 16.000000 | 4.000000  | 12.000000 | 40.000000  |
|        09001 |  90015 | 10.000000 | 8.000000  | 6.000000  | 40.000000  |
|        09001 |  90014 | 15.000000 | 12.000000 | 2.000000  | 40.000000  |
|        09001 |  90013 | 15.000000 | 12.000000 | 1.000000  | 40.000000  |
|        09001 |  90012 | 9.500000  | 0.250000  | 12.500000 | 40.000000  |
|        09001 |  90011 | 10.000000 | 0.250000  | 10.000000 | 40.000000  |
|        06003 |  60039 | 26.000000 | 18.000000 | 20.000000 | 40.000000  |
|        06003 |  60038 | 26.000000 | 12.000000 | 20.000000 | 40.000000  |
|        06003 |  60037 | 26.000000 | 10.000000 | 20.000000 | 40.000000  |
|        06003 |  60036 | 26.000000 | 8.000000  | 20.000000 | 40.000000  |
|        06003 |  60035 | 26.000000 | 6.000000  | 20.000000 | 40.000000  |
|        06003 |  60034 | 18.000000 | 10.000000 | 14.000000 | 40.000000  |
|        06003 |  60033 | 16.000000 | 8.000000  | 12.000000 | 40.000000  |
|        06003 |  60032 | 14.000000 | 8.000000  | 12.000000 | 40.000000  |
|        06003 |  60031 | 16.000000 | 4.000000  | 12.000000 | 40.000000  |
|        06001 |  60013 | 14.000000 | 8.000000  | 12.000000 | 40.000000  |
|        06001 |  60012 | 16.000000 | 4.000000  | 12.000000 | 40.000000  |
|        06001 |  60011 | 12.000000 | 4.000000  | 10.000000 | 40.000000  |
|        04001 |  40013 | 15.000000 | 25.000000 | 20.000000 | 70.000000  |
|        04001 |  40012 | 18.000000 | 18.000000 | 18.000000 | 40.000000  |
|        04001 |  40011 | 8.000000  | 5.000000  | 6.000000  | 40.000000  |
|        03008 |  30086 | 16.000000 | 8.000000  | 12.000000 | 40.000000  |
|        03008 |  30085 | 14.000000 | 8.000000  | 12.000000 | 40.000000  |
|        03008 |  30084 | 16.000000 | 4.000000  | 12.000000 | 40.000000  |
+--------------+--------+-----------+-----------+-----------+------------+

What I want to do is find the largest box for each carton group. That is easy, this is how you do that

select
CARTON_GROUP
,MAX(HEIGHT*WIDTH*LENGTH) Cube
from CARTONS
group by CARTON_GROUP

But I also want to include the Size (name) in that calculation. But I can't do that because I can't include the size in the group by because since it is unique, it won't be group by correctly.

And I can't do an aggregrate function over the size like

,max(Size) size

Because I don't want the max of the size over the group by, I want the size of the biggest box (max of the dimensions) over the group by.

And I can't just join that table with the cube back to the original table because what if two different boxes have the same cube calculation.

Is there any way to do this?

Upvotes: 3

Views: 58

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269593

Use row_number():

select c.*, (height*width*length) as volume
from (select c.*,
             row_number() over (partition by carton_group order by height*width*length desc) as seqnum
      from CARTONS c
     ) c
where seqnum = 1;

In the case of ties, this returns one arbitrary carton with the max. Use rank() if you want all of them when there are ties.

If you have a limit on the dimensions, put that in the subquery:

select c.*, (height*width*length) as volume
from (select c.*,
             row_number() over (partition by carton_group order by height*width*length desc) as seqnum
      from CARTONS c
      where max_weight <= 40
     ) c
where seqnum = 1;

Upvotes: 3

GMB
GMB

Reputation: 222432

You can filter with a correlated subquery:

select c.*
from cartons c
where c.height * c.width * c.size = (
    select max(c1.height * c1.width * c1.size)
    from cartons c1
    where c1.carton_group = c.carton_group

)

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

use row_number()

select a.* from (select *,
row_number() over(partition by CARTON_GROUP order by (HEIGHT*WIDTH*LENGTH) desc) rn from CARTONS
) a where a.rn=1

Upvotes: 2

Related Questions