Geek Answers Handbook

Get maximum / minimum record

The rather complex SQL query that I was working on made me think about the SQL (ANSI) constraint:

Is there a way to get the maximum or minimum record with respect to an arbitrary order?

In other words:

Given such a request:

SELECT * FROM mytable WHERE <various conditions> ORDER BY <order clause>

Is it possible to write a query that returns only the first row (perhaps by converting the order clause to another)?

I know that you can do this using LIMIT (MySQL) / ROWNUM (Oracle) or similar, but this is not standard SQL.

I also know that you can do this by extracting the maximum value that interests you in the subquery (using MIN () / MAX ()), and then use this result as a criterion in your main SELECT, i.e.:

 SELECT * FROM mytable WHERE <various conditions> AND myMaxColumn=( SELECT MAX(myMaxColumn) FROM mytable WHERE <various conditions> )

But this only works if I want to sort by one column. I do not see the possibility of generalizing this to several columns (except for the nesting of the above solution, but this will mean 2 ^ n SELECT when ordering by n coluns).

So, is there a better way in standard SQL than nesting multiple subqueries?

A related question is asked in Create an SQL query to retrieve the latest records . However, the answers there suggest using LIMIT and friends or using a subquery with MAX (), as explained above, both of which are not the solution to my question.

+4
source share
2 answers

SQL:2003 defines the concept of window functions, one of which is:

 SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY col1, col2, col3) AS rn FROM mytable ) q WHERE rn = 1

will return you this first record.

So far, it is supported by SQL Server , Oracle , and since Jul 01, 2009 , PostgreSQL 8.4

Note, however, that ROW_NUMBER() in Oracle less efficient than its own way of restricting records (e.g. ROWNUM ).

See this blog post for a comparison of performance:

SQL:2008 offers another suggestion:

 SELECT * FROM mytable ORDER BY col1, col2, col3 FETCH FIRST 1 ROW ONLY

but now this exact syntax is only supported by DB2 ( AFAIK ).

+10
source

If you understood correctly, I think that you are looking for an OVER clause that allows you to split result sets defined as part of the ANSI SQL 2003 Standard .

It is not very consistently implemented on RDBMS platforms.

+8
source

More articles:


All Articles