group by

[Szhark]

I'm trying to grab duplicates from a table (answered in a previous post..) but I'm having trouble getting another column out of the table:
(segments [segmentid, user_id, typeid])

SELECT b.user_id, count(b.user_id) from segments b
WHERE b.typeid = 100
group by b.user_id
having count(b.user_id) > 1

That basically grabs all the duplicate user_id's of ppl with a typeid of 100. But I want to grab the unique identifier[segmentid] so I can delete them from the table. But everytime I add that field, i get a 'not a group by function' error.. and I dont want to group the segmentid because it wont grab the duplicates.

SELECT b.segmentid, b.user_id, count(b.user_id) from segments b
WHERE b.typeid = 100
group by b.user_id
having count(b.user_id) > 1

Any help is appreciated. BTW: I LOVE THIS FORUM!!

[tamilselvan]

Try the following statement:

SELECT b.segmentid, b.user_id, count(b.user_id) from segments b
WHERE b.typeid = 100
group by b.segmentid, b.user_id
having count(b.user_id) > 1

[ssu4716]

Try this:

select s.userid, s.segmentid
from segments s,
(SELECT user_id, count(user_id) from segments
WHERE typeid = 100
group by user_id
having count(user_id) > 1) b
where s.typeid = 100 and s.userid=b.userid;

I could not test this query but I think there is a good probability that this will give you the output that you are looking for.

Thanks.

Syed

[Highlander]

this should work

select *
from table_1
where (col_1, col_2, col_3) in
(select col_1, col_2, col_3
from table_1
group by col_1, col_2, col_3
having count(*) > 1);


You don't need the count() in the select statment.