CODEWITHSUNDEEP
mysql
sb15
sb15

Reputation: 67

query to retrieve the id of row containing duplicate value in a field

let us assume the table

id  usr_id    starttime
1   11        2012-12-12 09:00:00
2   12        2012-12-12 09:00:00
3   13        2012-12-13 12:00:00
4   14        2012-11-12 10:00:00

now the starttime is same for usr_id 11 and 12 so I need to retrieve these 2 ids with the same starttime values.

but when I tried using the following query it just displays the first value that is 11

SELECT
    y.usr_id
FROM req y
INNER JOIN (SELECT usr_id,, COUNT(*) AS CountOf
            FROM req
            GROUP BY starttime
            HAVING COUNT(*)>1
           ) dt ON y.usr_id=dt.usr_id

Upvotes: 0

Views: 40

Answers (2)

Hardik Bhalani
Hardik Bhalani

Reputation: 863

try this.. GROUP_CONCAT(expr)

select group_concat(usr_id) from req group by starttime

Upvotes: 1

Joachim Isaksson
Joachim Isaksson

Reputation: 180927

You could use a simple self join to get the id's with duplicate times;

SELECT t1.usr_id 
FROM req t1
JOIN req t2
  ON t1.starttime = t2.starttime
 AND t1.id <> t2.id;

An SQLfiddle to test with.

Upvotes: 2

Related Questions