PostgreSQL, SELECT from max id

Question

Using libpq on PG 9.1, I am trying to write a query to get the values ​​from the string with the highest index 'my_id':

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

This gives me an error:

ERROR: aggregates not allowed in WHERE clause ...

How to write such a request?

Answer 1

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

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

Answer 2

Just order my_id and take only the first entry with limit 1

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

Another less effective way would be

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

Answer 3

Sub request can help you

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

Answer 4

 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 

Answer 5

when you have a pointer to my_id, those that have a subquery should be faster. when you do not have an index, enter "order by". (depends on the size of the database, if necessary)