Is there something equivalent to argmax in SQL?

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