CODEWITHSUNDEEP
sqlpostgresql
ThreeAccents
ThreeAccents

Reputation: 1882

PostgreSQL Select latest and by other clause

I need to write a query that retrieves the latest column and any column that has a specific value.

So I have a table draws

draws:
 - id
 - created_at
 - state

I want to select all draws that are of state pending along with the latest draw no matter what state it has.

My short term solution was writing 2 queries and merging the results within the app logic but I'm sure there's a way to do it in one query.

I've attempted using a union query but I do not get the desired results

select * FROM draws where state = 'pending' 
UNION
select * from draws order by created_at desc limit 1

Upvotes: 0

Views: 31

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You can use a correlated subquery:

select d.*
from draws d
where d.state = 'pending' or
      d.created_at = (select max(d2.created_at)
                      from draws d2
                     );

Upvotes: 1

Related Questions