CODEWITHSUNDEEP
sqlpostgresql
Michael
Michael

Reputation: 845

Grab all rows in a table and grab one row where condition

I have an integer column that's set to 1 for nearly all rows in my table, 2 for about 10 rows, and 3 for 6 rows. I want a to select all rows from my table where that column is not equal to 3, but grab the most recently updated row where it is equal to 3. I also have an updated_at column that is a date column.

Here is the sql I've come up with so far.

SELECT * 
  FROM mytable 
  LEFT JOIN (SELECT * 
               FROM mytable 
              WHERE spec_id=3 
              ORDER BY updated_at DESC LIMIT 1) mytable2 
         ON mytable.id=mytable2.id

However, this statement grabs all 6 of my rows where spec_id is set to 3.

Please help.

Upvotes: 0

Views: 92

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65105

Just use union all

(SELECT * FROM mytable WHERE spec_id=3 ORDER BY updated_at DESC LIMIT 1)
UNION ALL
 SELECT * FROM mytable WHERE spec_id!=3

Upvotes: 2

Related Questions