A rather complicated SQL query I was working on got me thinking about a limitation of (ANSI) SQL:
Is there a way to retrieve a record that is maximal or minimal with respect to an arbitrary ordering?
In other words:
Given a query like this:
SELECT * FROM mytable WHERE <various conditions> ORDER BY <order clause>
is it possible to write a query that returns only the first row (possibly by transforming the order clause into something else)?
I know you can do this using LIMIT (MySQL) / ROWNUM (Oracle) or similar, but that's not standard SQL.
I also know you can do this by fetching the max/min value you are interested in in a subquery (using MIN()/MAX()), then use that 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 that only works if I want to sort by a single column. I see no way to generalize this to multiple columns (other than nesting the above solution, but that would mean 2^n SELECTs when ordering by n coluns).
So is there a better way in standard SQL than nesting multiple subselects?
A related question is asked in Create a SQL query to retrieve most recent records. However, the answers there suggest either using LIMIT & friends, or to use a subquery with a MAX() as explained above, both of which are not solutions to my question.
SQL:2003 defines concept of window functions, one of which:
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.
As for now, it's supported by SQL Server, Oracle and since Jul 01, 2009, by PostgreSQL 8.4
Note, however, that ROW_NUMBER() in Oracle is less efficient than the native way to limit records (i. e. ROWNUM).
See this article in my blog for performance comparison:
SQL:2008 offers another clause to do this:
SELECT *
FROM mytable
ORDER BY
col1, col2, col3
FETCH FIRST 1 ROW ONLY
, but as for now, this exact syntax is supported by DB2 only (AFAIK).