CODEWITHSUNDEEP
sqlpostgresql
PurpleVermont
PurpleVermont

Reputation: 1241

Find all rows that have a value in a certain column that occurs more than once

Suppose I have a database with two columns, an id (bigint) and text (text). I want to find all rows in the database that have an id that occurs in more than one row. How could I do that?

Example:

1; foo  
2; bar 
3; baz
2; fubar
4; blah
3; bleh

Desired output:

2; bar 
2; fubar
3; baz
3; bleh

I'm quite new to SQL. I have a notion that I want something along the lines of

SELECT id,text FROM mytable ORDER BY id ASC;

But I have no idea how to eliminate the rows where the id is unique. I need essentially the opposite of SELECT DISTINCT. Thanks in advance!

Upvotes: 0

Views: 1045

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

You can do this with window functions:

select id, text
from (select t.*, count(*) over (partition by id) as cnt
      from table t
     ) t
where cnt > 1;

Upvotes: 4

rfusca
rfusca

Reputation: 7705

Something like this:

WITH more_than_one AS (SELECT id,count(*) FROM table1 GROUP BY id HAVING count(*) > 1)
SELECT * FROM table1 JOIN more_than_one ON table1.id = more_than_one.id;

Upvotes: 1

Related Questions