-
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!!
-
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
-
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
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|