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?
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
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)
Sub query may help you
SELECT my_id, col2, col3 FROM mytable WHERE my_id = (select MAX(my_id) FROM mytable)
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
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)
LIMIT 1 queries where an index is applicable.