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

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'

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

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.

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

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related