How do I eliminate duplicate lines?
im back with my original request and i only have one question (ps: i know i need to vote and register and i promise i will do it today)
With the following query (t-sql) I am getting correct results, except there are now duplicates.
I am reading and think I can use the PARTITION BY syntax. Could you show me how to enable the PARTITION BY syntax?
WITH CALC1 AS (SELECT OTQUOT, OTIT01 AS ITEMS, ROUND(OQCQ01 * OVRC01,2) AS COST
FROM
@LIB@.RTQOTA
WHERE OTIT01 <> ''
UNION ALL
...
SELECT OTQUOT, OTIT10 AS ITEMS, ROUND(OQCQ10 * OVRC10,2) AS COST
FROM
@LIB@.RTQOTA
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 @LIB@.ITMCON 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 duplicates? thanks to Bill and everyone else for your help (I'm still learning!)
a source to share
You can either change PARITION BY to something like
(PARTITION BY OTQUOT, C.OTRDSC, ITEMS ORDER BY COST DESC)
and create WHERE CLAUSE
WHERE RN = 1
or you can just use ( SELECT DISTINCT )
DISTINCT
Indicates that only unique strings can appear in the result set. Null values ββare considered equal for the purposes of the DISTINCT keyword.
SELECT DISTINCT OTQUOT, DESC, ITEMS
Also, as a matter of thought, why are you selecting columns in SUB SELECt that you are not using in your output selection ( B.IXRPGP AS GROUP
)?
a source to share