CODEWITHSUNDEEP
sqlsql-servert-sqlgreatest-n-per-groupwindow-functions
HurzKapurz
HurzKapurz

Reputation: 3

selecting max value from table with two variable colums (microsoft SQL)

i´m working with a table that looks like this:

Start https://i.sstatic.net/uibc3.png

My desired result would look like this:

Result https://i.sstatic.net/v0sic.png

So i´m triyng to select the max value from two "combined" colums. If the values are the same amount (Part C), the outcome doesn't matter. I tried to order the table by max value and then using distinct but the result didn't turn out as expected

Could you please offer a solution or some insight to this? Thanks in advance!

Upvotes: 0

Views: 58

Answers (2)

Thiyagu
Thiyagu

Reputation: 1330

You can achieve this by using SUB Query

DECLARE @T TABLE(
PART VARCHAR(50),
ZONE VARCHAR(10),
Amt INT)


Insert Into @T Values ('PartA','71H',1),('PartA','75H',2),('PartB','98D',1),('PartB','98A',3),('PartC','75H',1),('PartC','52H',1)

SELECT M.PART,MIN(M.Zone) AS ZONE,S.AMOUNT
FROM @T M
INNER JOIN (
SELECT Part,MAX(Amt) as AMOUNT From @T
GROUP BY PART) S ON S.AMOUNT=M.Amt AND S.PART=M.PART
GROUP BY M.PART,S.AMOUNT
ORDER BY M.PART

Upvotes: 0

GMB
GMB

Reputation: 222482

Use row_number():

select *
from (
    select t.*, row_number() over(partition by part order by amount desc, zone) rn
    from mytable t
) t
where rn = 1

For each part, this gives you the row with the highest amount; if there are top ties, column zone is used to break them.

If you want to allow ties, then use rank() instead, like:

rank() over(partition by part order by amount desc) rn

Upvotes: 1

Related Questions