Is there something equivalent to argmax in SQL?

Question

In a more general sense: is there a function that will allow me to find the entire row where a value in Column X is the max value of the column?

Answer 1

Not a specific function, no.

There are numerous ways to write the query, depending on needs and what functionality is supported by the database.

The Subquery:

This approach risks returning more than one row if any share the same value:

SELECT x.*   FROM YOUR_TABLE x  WHERE x.column = (SELECT MAX(y.column)                      FROM YOUR_TABLE y) 

The Self Join:

This approach risks returning more than one row if any share the same value:

SELECT x.*   FROM YOUR_TABLE x   JOIN (SELECT MAX(t.column) AS max_col           FROM YOUR_TABLE t) y ON y.max_col = x.column 

LIMIT/TOP:

SQL Server supports TOP:

  SELECT TOP 1           x.*     FROM YOUR_TABLE x ORDER BY x.column DESC 

MySQL & PostgreSQL support LIMIT:

  SELECT x.*     FROM YOUR_TABLE x ORDER BY x.column DESC    LIMIT 1 

Analytic - ROW_NUMBER():

This will return one row, and can be configured to provide the highest (or lowest) value per grouping. However, this functionality is Oracle 9i+, SQL Server 2005+, and PostgreSQL 8.4+.

SELECT x.*   FROM (SELECT y.*,                ROW_NUMBER() OVER (ORDER BY y.column DESC) AS rank           FROM YOUR_TABLE y) x  WHERE x.rank = 1  

Analytic - DENSE_RANK():

This can return multiple rows if they share the same value, and can be configured to provide the highest (or lowest) value per grouping. However, this functionality is Oracle 9i+, SQL Server 2005+, and PostgreSQL 8.4+.

SELECT x.*   FROM (SELECT y.*,                DENSE_RANK() OVER (ORDER BY y.column DESC) AS rank           FROM YOUR_TABLE y) x  WHERE x.rank = 1  

Answer 2

SELECT * FROM mytable WHERE mycolumn = (   SELECT MAX(mycolumn) FROM mytable )