SQL select a row by highest value from a group

Question

I'm trying to select the highest value(calculated) of a group with distinct selection of other columns

according to the table-data below, i want to select the rows, which have the highest amount (Qty-Plan) and a distinct selection of Len and Wid

Table data is as follow

+-----------+-----------+---------+---------+------------+---------+
|   Ident   |   Name    | Len     |   Wid   |      Qty   |    Plan |
+-----------+-----------+---------+---------+------------+---------+
|  12345    | Name1     | 1500    |    1000 |         20 |       5 |
|  23456    | Name1     | 1500    |    1000 |         30 |      13 |
|  34567    | Name1     | 2500    |    1000 |         10 |       2 |
|  45678    | Name1     | 2500    |    1000 |         10 |       4 |
|  56789    | Name1     | 1500    |    1200 |         20 |       3 |
|  00001    | Name2     | 1500    |    1200 |         10 |       6 |
|  00002    | Name2     | 1500    |    1200 |         20 |       7 |
|  00003    | Name3     | 1500    |    1200 |         30 |       5 |
|  00004    | Name3     | 1500    |    1200 |         40 |       4 |
+-----------+-----------+---------+---------+------------+---------+

with my query i cant erase the "lower" values:

select a.Ident ,a.Name, a.Len,a.Wid, a.Qnt-a.Plan as Amount
from table a
join (select ident, max(Qnt - Plan) Amount
      from table
      where Name = 'Name1'
      group by Ident, Len, Wid) b
  on b.Ident = a.Ident and b.Amount = a.Qnt-a.Plan
order by Amount desc

off-topic question: why i cant use -> where b.Amount = a.Amount (he does not know a.Amount ) ???

my desired select should look like:

+-----------+-----------+---------+---------+------------+
|   Ident   |   Name    | Len     |   Wid   |   Amount   |
+-----------+-----------+---------+---------+------------+
|  56789    | Name1     | 1500    |    1200 |         18 |
|  23456    | Name1     | 1500    |    1000 |         17 |
|  34567    | Name1     | 2500    |    1000 |          8 |
+-----------+-----------+---------+---------+------------+

thanks a lot in advance

The sub-query looks strange. You typically GROUP BY the same columns as you SELECT, except those who are arguments to set functions. — jarlh
– jarlh, Commented Jun 7, 2019 at 7:39
You want the rows, which have the highest amount (Qty-Plan), but in the desired select I see amounts 17, 8, 18. What is the highest? — Maxim Tkachenko
– Maxim Tkachenko, Commented Jun 7, 2019 at 7:48
@mtkachenko yes, the highest "Amount" of "Len"&"Wid" combination.. he should not show the rows with the ident "12345" and "45678" because for "12345" the Amount is only "15" and has same "Len" and "Wid" as "23456" — DDXEbeling
– DDXEbeling, Commented Jun 7, 2019 at 7:52
For the combination Len/Wid 1500/1200, I would expect Ident 00004 since it has an amount of 40-4=36 — Robert Kock
– Robert Kock, Commented Jun 7, 2019 at 7:56
@Robert Kock, but only Name = 'Name1' is selected... otherwise you are right — DDXEbeling
– DDXEbeling, Commented Jun 7, 2019 at 7:57

Robert Kock · Accepted Answer · 2019-06-07 08:07:00Z

1

It's not clear what kind of database you're using, but this solution should work on any DB:

SELECT tab.Ident,
       tab.Name,
       tab.Len,
       tab.Wid,
       (tab.Qty - tab.Plan) AS Amount
FROM   (SELECT   Name,
                 Len,
                 Wid,
                 MAX(Qty-Plan) AS Amount
        FROM     my_table
        GROUP BY Name,
                 Len,
                 Wid
       ) AS grouped
JOIN   my_table tab
  ON   grouped.Name = tab.Name
 AND   grouped.Len = tab.Len
 AND   grouped.Wid = tab.Wid
 AND   grouped.Amount = (tab.Qty - tab.Plan)
 AND   tab.Name = 'Name1'

answered Jun 7, 2019 at 8:07

Robert Kock

6,0482 gold badges14 silver badges23 bronze badges

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

Comments

Shawn · Accepted Answer · 2019-06-07 09:00:56Z

1

Another approach, using window functions to simplify things:

SELECT ident, name, len, wid, qnt - [plan] AS amount
FROM  (SELECT *, row_number() OVER (PARTITION BY len, wid ORDER BY qnt - [plan] DESC) AS rn
       FROM test WHERE name = 'Name1') AS sq
WHERE rn = 1
ORDER BY amount DESC;

SQL Fiddle example.

answered Jun 7, 2019 at 9:00

Shawn

53.9k3 gold badges29 silver badges74 bronze badges

1 Comment

Gordon Linoff Over a year ago

Window functions are a much better approach. Simpler and generally better performing than a JOIN/GROUP BY alternative.

Collectives™ on Stack Overflow

SQL select a row by highest value from a group

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related