0

Consider a simple table for example purposes:

person(name varchar, age int)

I have a task to retrieve the oldest person. Which query is better in general for such problems?

Query 1: criteria with subquery for maximum value

SELECT p.* FROM person p WHERE p.age = (SELECT MAX(age) FROM person);

Query 2: ordering and selecting first row

SELECT p.* FROM person ORDER BY age DESC LIMIT 1;

I'm currently using PostgreSQL, but the question is I think platform independent.

  • 2
    Look at the execution plan (using explain analyze) and you will know. Btw: your two statements are not equivalent. The first one will return multiple rows if multiple person are the same age, the second one will not. – a_horse_with_no_name Jun 2 '15 at 13:41
  • @a_horse_with_no_name thank you, you're right of course, I forgot to note that "if we assume that only one person is the oldest" – Zoltán Tamási Jun 2 '15 at 13:50
  • In both cases the speed should be similar and mainly depend on indexing. – dnoeth Jun 2 '15 at 13:53
  • I will do some explain analyze when I have time to setup a test table. My guess - which I didn't want to include in the question - would be that first one should be faster on large tables because sorting seems to be more expensive than seq scan with aggregation. – Zoltán Tamási Jun 2 '15 at 13:57
  • @dnoeth I didn't mention indexing because that can change things quite much indeed, so for the matter of theoretical discussion I assumed that no indexing or other lower-level optimizations are applied. – Zoltán Tamási Jun 2 '15 at 13:59
0

I'm not sure if this is as platform independent as you think but I've been doing some fairly extensive testing on this in SQL Server recently and the first query was interestingly the fastest. This was comparing against a number of "tricks" in SQL Server including using CROSS APPLY and windowing functions. But again YMMV in PostgreSQL.

Unless I'm mistaken (I don't know PostgreSQL) your second query will actually come up faster but only because it's going to give you the age for one person.

0

My guess is query2 (not exactly the same as query 1 though), but you will have to check the plan to be sure. Btw:

FETCH FIRST 1 ROWS ONLY 

is the standard equivalent to LIMIT, it is supported by DB2 LUW (>= 7), Oracle (>= 12c), PostgreSQL (>= 8.4) and SQL server (>= 2012). Yet another option is to use an analytical function like rank:

SELECT age, name
FROM (
    SELECT age, name, rank() over (order by age desc) as rnk
    FROM person
) 
WHERE rnk = 1;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.