CODEWITHSUNDEEP
postgresqloptimization
A.Sidor
A.Sidor

Reputation: 287

Nested SQL Query Optimization

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.

Upvotes: 0

Views: 33

Answers (2)

Joachim Nielandt
Joachim Nielandt

Reputation: 155

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')

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 248125

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.

Upvotes: 2

Related Questions