Get value based on max of a different column grouped by another column [duplicate]

by

You can approach this using row_number():

select key, val
from (select t.*, row_number() over (partition by key order by num desc) as seqnum
      from table_name t
     ) t
where seqnum = 1;

Whether you consider this more “elegant” is probably a matter of taste.

I should point out that this is subtly different from your query. This is guaranteed to return one row for each key; yours could return multiple rows. If you want that behavior, just use rank() or dense_rank() instead of row_number().

More Related Contents:

  1. SQL function PIVOT with 2 column
  2. Oracle SQL pivot query
  3. Is there an Oracle SQL query that aggregates multiple rows into one row? [duplicate]
  4. How to handle a single quote in Oracle SQL
  5. Select count(*) from multiple tables
  6. Fetch the rows which have the Max value for a column for each distinct value of another column
  7. REGEX to select nth value from a list, allowing for nulls
  8. Oracle: loading a large xml file?
  9. IF EXISTS condition not working with PLSQL
  10. Group by alias (Oracle)
  11. Using pivot on multiple columns of an Oracle row
  12. Sleep function in ORACLE
  13. Solution to “cannot perform a DML operation inside a query”?
  14. Split comma separated values of a column in row, through Oracle SQL query
  15. oracle — Split multiple comma separated values in oracle table to multiple rows
  16. How to query a CLOB column in Oracle
  17. update columns values with column of another table based on condition [duplicate]
  18. how to select even records from a table in oracle?
  19. Oracle : select maximum value from different columns of the same row
  20. Split varchar into separate columns in Oracle
  21. Creating or simulating two dimensional arrays in PL/SQL
  22. Using Alias In When Portion of a Case Statement in Oracle SQL
  23. Oracle string aggregation
  24. How do I list all the columns in a table?
  25. Oracle: SQL query to find all the triggers belonging to the tables?
  26. oracle SQL how to remove time from date
  27. Oracle , != , ^= operators
  28. Product() aggregate function
  29. Re-order columns of table in Oracle
  30. Select latest row for each group from oracle

Leave a Comment