CODEWITHSUNDEEP
sqlpostgresqlaggregate-functions
Cracs
Cracs

Reputation: 525

Return duplicated rows by max column

I'm trying to get all rows duplicated (title) by max initID.

Here is my table :

  ID |      title      | revision | initID
  1  |    Mytitle 1    |    0     |  10
  2  |    Mytitle 1    |    1     |  10
  3  |    Mytitle 1    |    2     |  10
  4  |    Mytitle 1    |    0     |  20
  5  |    Mytitle 1    |    1     |  20

  6  |    Mytitle 2    |    0     |  30
  7  |    Mytitle 2    |    1     |  30

  8  |    Mytitle 3    |    0     |  40
  9  |    Mytitle 3    |    1     |  40
  10 |    Mytitle 3    |    0     |  50
  11 |    Mytitle 3    |    1     |  50
  12 |    Mytitle 3    |    2     |  50

  13 |    Mytitle 4    |    0     |  60

My goal is to check if there is multiple initID with the same title and get the duplicate lines with the MAX(initid).

For the example, I want to get this return from my request:

  4  |    Mytitle 1    |    0     |  20
  5  |    Mytitle 1    |    1     |  20
  10 |    Mytitle 3    |    0     |  50
  11 |    Mytitle 3    |    1     |  50
  12 |    Mytitle 3    |    2     |  50

If there is just one initID for a title I don't want to get the lines back.

Upvotes: 0

Views: 34

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use EXISTS with correlated subquery :

SELECT t.*
FROM table t
WHERE EXISTS (SELECT 1 FROM table t1 WHERE t.title = t1.title AND t1.initID <> t.initID) AND
      t.initID = (SELECT MAX(t1.initID) FROM table t1 WHERE t1.title  = t.title);

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

use corelated subquery

 select t.* from table_name t
 where exists( select 1 from table_name t2 where t1.title=t2.title 
                   having count(*)>1)
 and t1.initID=( select max(initID) from table_name t2 where t1.title=t2.title)

Upvotes: 0

Related Questions