Is there something equivalent to argmax in SQL?

In a more general sense开发者_JAVA百科: 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?

---

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 

---

Not very long time ago i spoke with my friend, and we discussed a little bit different but quite practical issue, about splitting some "table" into groups, and then finding argmax/argmin for each grope that we obtain. More particularly, let assume that we have a table where we store all versions of some entities (meaning history of all entities, and the active entity is the most recent one on it's(history's) top), so how can we most effectively select only active records from this table (more precisely - split into groups by entity_id (not row_id), and find argmax in each grope by version column). So since one of my first thoughts about "how to investigate this issue" was keywords "oracle sql argmax OR argmin" i am posting my comment here.

As my friend says me, they uses [PARTITION BY][PARTITION-BY] keyword for their means, and as i can see from fetch-the-row-which-has-the-max-value-for-a-column and getting-values-relating-to-the-max-and-min-rows-in-oracle there are two choices about using DENSE_RANK with "OVER (ORDER BY ...)" and then bound that rank to 1 (as described here and in "fetch-the-row-which-has-the-max-value-for-a-column") or use something like "max (version) over (partition by entity_id/not row_id/) max_version" and then bound "version = max_version" in where causer, like described in "getting-values-relating-to-the-max-and-min-rows-in-oracle" ("fetch-the-row-which-has-the-max-value-for-a-column"), and my friend says that they prefer exactly the second approach, as i understood, because it may require less calculation per each group, since it only finds a maximum value, and don't have to completely sort each group, on the other hand, if there will be more then one record with the same maximum value, this query will select not the only one arbitrary chosen row with max value of "ordering column" (in our case version) but all rows (from group) that have that maximum value (so that can return Argmax - not only as a single row, but also as a set of rows where it is achieved).

Also, as i understood the [KEEP][KEEP] [LAST]/[FIRST] keyword in [Oracle SQL][oracle-sql-ref] are aimed to cover argmin/armax use case, in both variations - with [GROUP BY][GROUP-BY] or [PARTITION BY][PARTITION-BY] grouping, but since they assumes that the result of Argmax and Argmin can hold multiple rows too(not only one row), you cant't just select "that row" using [KEEP]...[FIRST]/[KEEP]...[LAST] construction, instead you mast use some [aggregation function][Oracle-Sql-Aggregate-Functions], to "extract" some value from "those rows" (Argmax-rows), like (from [Oracle doc][FIRST-Analytic-Example])

MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct)
  OVER (PARTITION BY department_id) "Best"

where

KEEP (DENSE_RANK LAST ORDER BY commission_pct) OVER (PARTITION BY department_id)

provides you Argmax as a set of rows (over department_id-group, using commission_pct as a criteria), and

MAX(salary)

provides you final aggregation over those Argmax (possibly numerous) rows. ([Select row with Max Value][Select-row-with-Max-Value] - discussion was starting point for me to understand that).

Here are some reference articles.

oracle-sql-ref
Oracle-Sql-Aggregate-Functions
KEEP
FIRST
FIRST-Analytic-Example
LAST
Select-row-with-Max-Value
GROUP-BY
PARTITION-BY
ORDER-BY-Analysis

SQL Functions / query_partition_clause

DENSE_RANK
MAX
ROW_NUMBER

Here is some freely quoted snippets about the things mentioned above.

using [ROW_NUMBER] with [ORDER BY][ORDER-BY-Analysis] and [PARTITION BY][PARTITION-BY]

select row_id, entity_id, version, entity_value_1, entity_value_2
  from (select row_id, entity_id, version, entity_value_1, entity_value_2, 
          row_number()
            over (partition by entity_id order by version desc) as rank
        from Entities) as r
  where r.rank=1

using [MAX] with [PARTITION BY][PARTITION-BY]

select row_id, entity_id, version, max_version, entity_value_1, entity_value_2
  from (select row_id, entity_id, version, entity_value_1, entity_value_2, 
          max(version) over (partition by entity_id) as max_version
        from Entities) as r
  where r.version=r.max_version

using [KEEP][KEEP] [LAST]/[FIRST] for suppressing possibility of multiple rows with the same entity_id and version occurs in the result (among Argmax-rows selecting unique one with maximum row_id)

SELECT row_id, entity_id, version, max_version, entity_value_1, entity_value_2
  FROM (SELECT 
          row_id, entity_id, version, entity_value_1, entity_value_2, 
          MAX(row_id) 
            KEEP (DENSE_RANK LAST ORDER BY version ASC)
            OVER (PARTITION BY entity_id) 
            AS row_id_for_max_version
        FROM Entities) as r
  WHERE r.row_id=r.row_id_for_max_version

---

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

---

If I'm reading your question correctly, the following query should do it (assuming that our column names are a,b, and c and that a is the column that we're maximizing):

select a,b,c from table where a=(select max(a) from table);

Of course, if you have more than one row where the column a attains its maximum, then you'll get more than one row back from the query. If you want a unique row back, you can add something like "order by b,c limit 1", or use some other way to rank the rows in which a attains its max.

---

SELECT
    *
FROM
    YourTable
WHERE
    YourColum = (SELECT MAX(YourColumn) FROM YourTable)

---

Since the table name and column name are specified I am leaving them as fillers. Query would be :

SELECT *
  FROM <YOUR_TABLE>
WHERE <YOUR_COLUMN_NAME> = (SELECT MAX(<YOUR_COLUMN_NAME>)
  FROM <YOUR_TABLE>)

---

I think the following would select every row where the value in Column X is equal to the max Column X value:

SELECT * FROM [table] WHERE [Column X]=SELECT (MAX([Column X]) FROM [table])

---

--i have taken table name =max_search and column_name are A,B and C

and values in table are

A B C 10 20 30 60 40 50 80 90 70

Query:-

SELECT (CASE WHEN A > B AND A > C THEN 'A' WHEN B > A AND B > C THEN 'B' WHEN C > B AND C > A THEN 'C' END) AS "Column_Name", greatest(A,B,C) AS Max_Value FROM max_search;

Output:- Column_Name Max_Value C 30 A 60 B 90

---

Additionally other answers you can use ARRAY_AGG with GROUP BY:

SELECT 
   ARRAY_AGG(A ORDER BY B DESC LIMIT 1)[OFFSET(0)] AS argmax_a_b
FROM T
GROUP BY C