How to select records with maximum values in two columns?

by

Analytic functions are your friend:

SELECT   MAX( year    ) KEEP ( DENSE_RANK LAST ORDER BY year ASC, quarter ASC, message ASC ) AS year,
         MAX( quarter ) KEEP ( DENSE_RANK LAST ORDER BY year ASC, quarter ASC, message ASC ) AS quarter,
         MAX( message ) KEEP ( DENSE_RANK LAST ORDER BY year ASC, quarter ASC, message ASC ) AS message,
         type
FROM     info
GROUP BY type;

SQLFIDDLE

More Related Contents:

  1. How do I limit the number of rows returned by an Oracle query after ordering?
  2. How to create id with AUTO_INCREMENT on Oracle?
  3. When do I need to use a semicolon vs a slash in Oracle SQL?
  4. LISTAGG in Oracle to return distinct values
  5. Is there an Oracle SQL query that aggregates multiple rows into one row? [duplicate]
  6. How to handle a single quote in Oracle SQL
  7. Select count(*) from multiple tables
  8. Fetch the rows which have the Max value for a column for each distinct value of another column
  9. REGEX to select nth value from a list, allowing for nulls
  10. Concatenate and group multiple rows in Oracle [duplicate]
  11. Difference between BYTE and CHAR in column datatypes
  12. How to use BOOLEAN type in SELECT statement
  13. Call a stored procedure with another in Oracle
  14. create table with sequence.nextval in oracle [duplicate]
  15. Oracle: loading a large xml file?
  16. Group by alias (Oracle)
  17. Sleep function in ORACLE
  18. Split comma separated values of a column in row, through Oracle SQL query
  19. Foreign Key Creation issue in Oracle
  20. Custom Order in Oracle SQL
  21. How do I list all tables in a schema in Oracle SQL?
  22. Is there a version control system for database structure changes?
  23. oracle sql: update if exists else insert [duplicate]
  24. Oracle: Updating a table column using ROWNUM in conjunction with ORDER BY clause
  25. Compare Strings ignoring accents in SQL (ORACLE)
  26. Return number from Oracle Select statement after parsing date
  27. How do you interpret a query’s explain plan?
  28. How to call Oracle MD5 hash function?
  29. SQL Server equivalent of WM_CONCAT function [duplicate]
  30. Oracle: ‘= ANY()’ vs. ‘IN ()’

Leave a Comment