September 26, 2010 at 8:22 am
I have a query which returns duplicate date in 1 column as such (c1 = Column 1; c2 = Column 2)
TABLE
c1 | c2
A | X
A | Y
B | Z
I need to rewrite the query so it returns the following:
A | X
B | Z
OR
A | Y
B | Z
The goal is to eliminate the duplicate data in C1. So if it was
B | X
B | Y
A | Z
The goal would be for the query to return
B | X
A | Z
OR
B | Y
A | Z
Also, duplicate data does not exist in c2
Is the following the correct query to write this? (I do not have SQL 2K5 at my house to run this)
SELECT TOP 1 c1, c2
FROM table
WHERE COUNT(c1) > 1
UNION ALL
SELECT c1, c2
FROM table
WHERE COUNT(c1) = 1
the 1st half of the UNION ALL statement would return just: (I'm doing this in my head)
A | X
because only the first row of # rows returned (A being in c1 more than 1)
While the 2nd half of the UNION ALL statement would return because B satisfies COUNT(c1) = 1
B | Z
Could someone comment if I'm doing this correctly or if there is a way of doing it better?
I appreciate your help!
September 26, 2010 at 9:47 am
try this:
; with numbered_dupes_cte as
(
select rn = row_Number() over (partition by c1, c2),
c1, c2
from table
)
select * from numbered_dupes_cte where rn = 1
September 27, 2010 at 7:00 am
Is it possible to translate this into Access 2003 DB could interpret?
Once again, I appreciate your response. My code apparently does not take
September 27, 2010 at 7:13 am
umanpowered (9/26/2010)
...The goal is to eliminate the duplicate data in C1. So if it was
B | X
B | Y
A | Z
The goal would be for the query to return
B | X
A | Z
OR
B | Y
A | Z
...
To get
B | X
A | Z
try:
Select C1, MIN(C2) AS C2
From [Table]
Group By C1
To get
B | Y
A | Z
you an use:
Select C1, MAX(C2) AS C2
From [Table]
Group By C1
September 27, 2010 at 7:33 am
Euguene, IT WORKS! I appreciate your help. I didn't think about the MIN function. 🙂
September 27, 2010 at 7:48 am
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply