Wednesday, March 7, 2012

displaying all records of duplicates

HI all I have this table below and i need to get records from col1 >1 and col2 >1

col1 col2 language
1 a s
1 b p
3 c e
3 c f

SELECT col1, col2, language COUNT(*) AS Expr1
FROM table1
GROUP BY col1, col2
HAVING (COUNT(*) > 1)

with the above query it gives me this

col1 col2 expr1
3 c 2

the language column is not displayed what i want is not the exact count but all the records which is duplicate and must resemble the result below

col1 col2 expr1 language
3 c 2 e
3 c 2 f

I need the result above inorder to normalize the table and bring out the language part into another table

thanksSELECT t.col1, t.col2, d.daCount, t.language
FROM table1 AS t
INNER
JOIN (
SELECT col1, col2, COUNT(*) AS daCount
FROM table1
GROUP BY col1, col2
HAVING COUNT(*) > 1
) as d
ON c.col1 = t.col1
AND d.col2 = t.col2|||thanks Rudy

Got it...

No comments:

Post a Comment