Retrieve maximal / minimal record

Question

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.

Answer 1

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:

• Oracle: ROW_NUMBER vs. ROWNUM

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

Answer 2

If I've understood you correctly, I think you're looking for the OVER clause, which enables you to partition result sets, defined as part of the ANSI SQL 2003 standard.

It's not very consistently implemented across RDBMS platforms.