How to eliminate duplicate rows?

Question

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!)

Answer 1

You can either change your PARITION BY to something like

(PARTITION BY OTQUOT, C.OTRDSC, ITEMS ORDER BY COST DESC)

and create a WHERE CLAUSE

WHERE RN = 1

or you could simply use a (SELECT DISTINCT)

DISTINCT

Specifies that only unique rows 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 an after thought, why are you seleting columns in the SUB SELECt that you are not using in your output select (B.IXRPGP AS GROUP)?