CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
Wine Too
Wine Too

Reputation: 4655

PostgreSQL, SELECT from max id

By using libpq on PG 9.1, I am trying to write query to get values from row with highest index 'my_id':

SELECT my_id, col2, col3 
FROM mytable 
WHERE my_id = MAX(my_id)

That gives me error:

ERROR: aggregates not allowed in WHERE clause...

How to write such query properly?

Upvotes: 26

Views: 81222

Answers (5)

PSR
PSR

Reputation: 40318

SELECT my_id, col2, col3 FROM mytable WHERE my_id = (select MAX(my_id) FROM mytab)

or use

SELECT my_id, col2, col3 FROM mytable ORDER BY my_id DESC LIMIT 1

Upvotes: 4

GordonM
GordonM

Reputation: 31730

If your goal is to get the row with the highest my_id value, then the following query should achieve the same goal. 

SELECT my_id, col2, col3 
FROM mytable 
ORDER BY my_id DESC 
LIMIT 1

Upvotes: 53

Chris
Chris

Reputation: 159

when you have an index on my_id the ones with the subquery should be faster. when you dont have an index take the "order by". (obv. depends on database size if relevant)

Upvotes: 0

Hisham
Hisham

Reputation: 455

Sub query may help you 

SELECT my_id, col2, col3 FROM mytable WHERE my_id = (select MAX(my_id) FROM mytable)

Upvotes: 8

juergen d
juergen d

Reputation: 204746

Just order by my_id and take only the first record with limit 1

SELECT my_id, col2, col3
FROM mytable 
order by my_id desc
limit 1

Another but less performant way would be

SELECT my_id, col2, col3
FROM mytable 
where my_id = (select max(my_id) from mytable)

Upvotes: 14

Related Questions