PostgreSQL, SELECT from max id

sql postgresql greatest-n-per-group
Wine Too picture Wine Too · Jun 5, 2013 · Viewed 47.3k times · Source

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?

Answer

GordonM picture GordonM · Jun 5, 2013

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

Related questions

Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
SQL query to get most recent row for each instance of a given key

I'm trying to get the ip, user, and most recent timestamp from a table which may contain both the current ip for a user and one or more prior ips. I'd like one row for each user containing the most …

Read More
How to select id with max date group by category in PostgreSQL?

For an example, I would like to select id with max date group by category, the result is: 7, 2, 6 id category date 1 a 2013-01-01 2 b 2013-01-03 3 c 2013-01-02 4 a 2013-01-02 5 b 2013-01-02 6 c 2013-01-03 7 a 2013…

Read More