hi guys
im back with my original query and i just have one question please (ps: I know i have to vote and regsiter and I promise I will do that today)
With the following query (t-sql) I am getting the correct results, except that there are duplicates now.
I have been reading up and think I can use the PARTITION BY syntax - can you please show me how to incorporate the PARTITION BY syntax?
WITH CALC1 AS (SELECT OTQUOT, OTIT01 AS ITEMS, ROUND(OQCQ01 * OVRC01,2) AS COST
FROM
@
[email protected]
WHERE OTIT01 < ''
UNION ALL
...
SELECT OTQUOT, OTIT10 AS ITEMS, ROUND(OQCQ10 * OVRC10,2) AS COST
FROM
@
[email protected]
WHERE OTIT10 < ''
)
SELECT OTQUOT, DESC, ITEMS, RN
FROM (
SELECT OTQUOT, ITEMS, B.IXRPGP AS GROUP, C.OTRDSC AS DESC, COST, ROW_NUMBER() OVER
(PARTITION BY OTQUOT ORDER BY COST DESC) AS RN
FROM CALC1 AS A INNER JOIN @
[email protected] AS B ON (A.ITEMS = B.IKITMC) INNER JOIN
DATAGRP.GDSGRP AS C ON (B.IXRPGP = C.OKRPGP)
) T
RESULTS:
60408169 FENCING GNCPDCTP18BGBG 1
60408169 FENCING CGIFESHPD1795BG 2
60408169 FENCING GTTCGIBG 3
60408169 FENCING GBTCGIBG 4
How do I get rid of the duplicates?
thanks Bill and all the others for your help (I am still learning!)
