CODEWITHSUNDEEP
sqlsql-serverfiltermax
GHINOTE
GHINOTE

Reputation: 5

SQL Server - Filter by Highest Value

I am building a report that joins a few tables together. Everything came together perfectly minus one aspect. I need the cost2 field to show only the highest cost. Basically, an item goes through several cost changes through production, with the final cost being the highest. I need to query just that final cost. I know its a simple solution, but I have struggled more than I should on it.

Here's a simplified example of my query:
The MAX function does not work as I intended it. 

SELECT DISTINCT table1.item_no,
                table2.quantity,
                table2.date,
                table3.cost1,
                MAX(table4.cost2)
FROM table1
     INNER JOIN table2 ON table2.item_no = table1.item_no
     INNER JOIN table3 ON table3.item_no = table1.item_no
     INNER JOIN table4 ON table4.item_no = table1.item_no
WHERE table3.cost1 <> 0
GROUP BY table1.item_no,
         table2.quantity,
         table2.date,
         table3.cost1

Upvotes: 0

Views: 150

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

I think you want row_number():

SELECT t.*
FROM (SELECT t1.item_no, t2.quantity, t2.date, t3.cost1, t4.cost2,
           row_number() over (partition by t1.item_no order by t4.cost2 desc) as seqnum
      FROM table1 t1 INNER JOIN
           table2 t2
           ON t2.item_no = t1.item_no INNER JOIN
           table3 t3
           ON t3.item_no = t1.item_no INNER JOIN 
           table4 t4
           ON t4.item_no = t1.item_no
      WHERE t3.cost1 <> 0
     ) t
WHERE seqnum = 1;

Because you appear to want one row per item, the partition by only contains the item number. You will get all the other columns for the highest cost row.

Upvotes: 1

vETRI
vETRI

Reputation: 46

SELECT table1.item_no,
       table2.quantity,
       table2.date,
       table3.cost1,
       table4.cost2
FROM table1
     INNER JOIN table2 ON table2.item_no = table1.item_no
     INNER JOIN table3 ON table3.item_no = table1.item_no
     INNER JOIN table4 ON table4.item_no = table1,item_no
WHERE table3.cost1 <> 0
AND table4.cost4 = (SELECT MAX(a.cost4 ) 
                    FROM table4 a
                    WHERE
                        table4.item_no=a.item_no
                        GROUP BY a.item_no
                    )

Upvotes: 1

Related Questions