How to get max row from table

Question

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?

Answer 1

Adding ORDER BY COST DESC LIMIT 1 should do the trick, if I correctly understand your question.

Edit: If as a comment suggests your SQL engine does not support LIMIT, see this post on how to emulate LIMIT with Microsoft SQL Server (it will also work in other SQL-compliant engines such as Oracle, PostgreSQL or DB2 since it only uses SQL-standard constructs).

Answer 2

Use the ROW_NUMBER() windowing function to provide a means to select the top row. Note that you need to put it into a derived subquery because a WHERE clause cannot reference the query's own row number. You have to do that with an outside query:

WITH ( ...your UNION query... )
SELECT *
FROM (
  SELECT OTQUOT, ITEMS, COST, ROW_NUMBER() OVER (ORDER BY COST DESC) AS RN
  FROM CALC1
  WHERE OTQUOT = '04886471'
) T
WHERE T.RN = 1;

Also I wouldn't bother with the ORDER BY inside your UNION query.