T-SQL: Selecting Column Based on MAX(Other Column)

by

Using a self join:

This will return all the values with subkey values that match, in case there are multiples.

SELECT a.value
  FROM TABLE a
  JOIN (SELECT MAX(t.subkey) AS max_subkey
          FROM TABLE t
         WHERE t.key = 1) b ON b.max_subkey = a.subkey
 WHERE a.key = 1

Using RANK & CTE (SQL Server 2005+):

This will return all the values with subkey values that match, in case there are multiples.

WITH summary AS (
  SELECT t.*,
         RANK() OVER(ORDER BY t.subkey DESC) AS rank
    FROM TABLE t
   WHERE t.key = 1)
SELECT s.value
  FROM summary s
 WHERE s.rank = 1

Using ROW_NUMBER & CTE (SQL Server 2005+):

This will return one row, even if there are more than one with the same subkey value…

WITH summary AS (
  SELECT t.*,
         ROW_NUMBER() OVER(ORDER BY t.subkey DESC) AS rank
    FROM TABLE t
   WHERE t.key = 1)
SELECT s.value
  FROM summary s
 WHERE s.rank = 1

Using TOP:

This will return one row, even if there are more than one with the same subkey value…

  SELECT TOP 1
         t.value
    FROM TABLE t
   WHERE t.key = 1
ORDER BY t.subkey DESC

More Related Contents:

  1. Get top 1 row of each group
  2. SQL server select distinct rows using most recent value only
  3. Calculate a Running Total in SQL Server
  4. Not equal != operator on NULL
  5. SET NOCOUNT ON usage
  6. How can I get column names from a table in SQL Server?
  7. How can I select the first day of a month in SQL?
  8. Truncate (not round) decimal places in SQL Server
  9. Select statement to find duplicates on certain fields
  10. Is it possible to specify condition in Count()?
  11. Getting only Month and Year from SQL DATE
  12. Dynamic Pivot Columns in SQL Server
  13. WHERE Clause vs ON when using JOIN
  14. Is it possible to select sql server data using column ordinal position
  15. Selecting N rows in SQL Server
  16. @@IDENTITY, SCOPE_IDENTITY(), OUTPUT and other methods of retrieving last identity
  17. SQL “select where not in subquery” returns no results
  18. How to split string using delimiter char using T-SQL?
  19. Calculate the number of records for each date between 2 dates
  20. T-SQL: How to use parameters in dynamic SQL?
  21. How to compare datetime with only date in SQL Server
  22. I want to use CASE statement to update some records in sql server 2005
  23. Split words with a capital letter in sql
  24. How to return a incremental group number per group in SQL
  25. Eliminate and reduce overlapping date ranges
  26. scope_identity vs ident_current
  27. SQL Server SELECT INTO @variable?
  28. Select a column if other column is null
  29. Date serial in SQL?
  30. SQL Server: +(unary) operator on non-numeric Strings

Leave a Comment