How to get max row from table
- by Odette
HI
I have the following code and a massive problem:
WITH CALC1 AS (
SELECT OTQUOT, OTIT01 AS ITEMS, ROUND(OQCQ01 * OVRC01,2) AS COST
FROM @[email protected] WHERE OTIT01 <> ''
UNION ALL
SELECT OTQUOT, OTIT02 AS ITEMS, ROUND(OQCQ02 * OVRC02,2) AS COST
FROM @[email protected] WHERE OTIT02 <> ''
UNION ALL
SELECT OTQUOT, OTIT03 AS ITEMS, ROUND(OQCQ03 * OVRC03,2) AS COST
FROM @[email protected] WHERE OTIT03 <> ''
UNION ALL
SELECT OTQUOT, OTIT04 AS ITEMS, ROUND(OQCQ04 * OVRC04,2) AS COST
FROM @[email protected] WHERE OTIT04 <> ''
UNION ALL
SELECT OTQUOT, OTIT05 AS ITEMS, ROUND(OQCQ05 * OVRC05,2) AS COST
FROM @[email protected] WHERE OTIT05 <> ''
ORDER BY OTQUOT ASC
)
SELECT OTQUOT, ITEMS, MAX(COST)
FROM CALC1
WHERE OTQUOT = '04886471'
GROUP BY OTQUOT, ITEMS
result:
04886471 FEPO5050WCGA24 13.21
04886471 GFRK1650SGL 36.21
04886471 FRA7500GA 12.6
04886471 CGIFESHAZ 11.02
04886471 CGIFESHPDPR 11.79
04886471 GFRK1350DBL 68.23
04886471 RET1.63825GP 32.55
04886471 FRSA 0.12
04886471 GFRK1350SGL 55.94
04886471 GFRK1650DBL 71.89
04886471 FEPO6565WCGA24 16.6
04886471 PCAP5050GA 0.28
04886471 FEPO6565NCPAG24 0.000000
How can I get the result of the row with the Itemcode that has the highest value?
In this case I need the result:
04886471 GFRK1650DBL 71.89
but i dont know how to change my code to get that - can anybody please help me?
