is there any better way to write this query to be optimized?
SELECT * FROM data d
WHERE d.id IN (SELECT max(d1.id)
FROM data d1
WHERE d1.name='A'
AND d1.version='2')
I am not so good with SQL.
Add a comment
|
2 Answers
With PostgreSQL v13, you can do it like this:
SELECT * FROM data
WHERE name = 'A'
AND version = '2'
ORDER BY is DESC
FETCH FIRST 1 ROWS WITH TIES;
That will give you all rows where id is the maximum.
If id is unique, you can use FETCH FIRST 1 ROWS ONLY or LIMIT 1, which will also work with older PostgreSQL versions.
Comments
Apart from other answers that are equally interesting / correct, IN is typically a non-performant keyword. You can remove it by using a slightly different way of writing your own query:
SELECT * FROM data d
WHERE d.name = 'A' and d.version = '2' and
d.id = (SELECT max(d1.id) FROM data d1 WHERE d1.name='A' AND d1.version='2')
