getting rid of duplicates

Last Post 26 Jan 2006 04:50 PM by mwesch. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
natasha
New Member
New Member

--
26 Jan 2006 12:15 PM
Table structure
Test1
ID1 name1 name2 id2
111 aaa bbb 123
111 abab baba 123
111 aabb bbbaa 122
222 ccc dddd 345
222 cdcd dcdc 349
333 eeee ffff 123

results should be
111 aaa bbb 123
222 cdcd dcdc 349
333 eeee ffff 123

My query works ...but it still gives me duplicate for id1 test1.
here is the query:
select * from test1 t
where ID2 = (select max(id2) from test1 t2
where t.id1 = t2.id1
group by id1)

here is the result Iget

111 aaa bbb 123 -- I only need one of this record does not matter which one.
111 abab baba 123 -- I only need one of this record does not matter which one.
222 cdcd dcdc 349
333 eeee ffff 123


I have done this before using top but right now my brains are fried and can't think of an elegant way of doing it.

Thanks for any help,
Nat
mwesch
New Member
New Member

--
26 Jan 2006 04:50 PM
You can nest a series of group by statements and absolutely determine which record you want.

select a2.id1, a2.name1, max(a2.name2) as name2, a2.id2
from Test1 a2
inner join
(
select x2.id1, x2.id2, max(x2.name1) name1
from Test1 x2
inner join
(
select id1, max(id2) as id2
from Test1
group by id1
) as x1
on x2.id1 = x1.id1
and x2.id2 = x1.id2
group by x2.id1, x2.id2
) as a1
on a2.id1 = a1.id1
and a2.id2 = a1.id2
and a2.name1 = a1.name1
group by a2.id1, a2.name1, a2.id2
natasha
New Member
New Member

--
27 Jan 2006 05:16 AM
Thank you for yout time and help. I totally appreciate it.
This solution works but it will take very long if executed against millions of records. There has to be a better way of doing this.
Thanks,
Nat
nosepicker
New Member
New Member

--
27 Jan 2006 07:58 AM
You could also do it this way, although I don't know if it's necessarily any faster than Michael's query. You'll have to try them out to see:

SELECT A.*
FROM test1 AS A
JOIN test1 AS B
ON A.id1 = B.id1 AND A.id2 <= B.id2 AND A.name1 <= B.name1
GROUP BY A.id1, A.name1, A.name2, A.id2
HAVING COUNT(*) = 1

You are not authorized to post a reply.

Acceptable Use Policy