How to eliminate duplicate rows?
Posted
by Odette
on Stack Overflow
See other posts from Stack Overflow
or by Odette
Published on 2010-04-21T02:56:56Z
Indexed on
2010/04/21
3:03 UTC
Read the original article
Hit count: 258
duplicates
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
WHERE OTIT01 <> ''
UNION ALL
...
SELECT OTQUOT, OTIT10 AS ITEMS, ROUND(OQCQ10 * OVRC10,2) AS COST
FROM
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!)
© Stack Overflow or respective owner
