Taking the record with the max date

Question

Let s assume I extract some set of data   i e   SELECT A  date FROM table   I want just the record with the max date  for each value of A   I could write   SELECT A  col date   FROM TABLENAME t ext  WHERE col date    SELECT MAX  col date                       FROM TABLENAME t in                     WHERE t in A   t ext A    But my query is really long    is there a more compact way using ANALYTIC FUNCTION to do the same

User · Accepted Answer

The analytic function approach would look something like

SELECT a, some_date_column
  FROM (SELECT a,
               some_date_column,
               rank() over (partition by a order by some_date_column desc) rnk
          FROM tablename)
 WHERE rnk = 1

Note that depending on how you want to handle ties (or whether ties are possible in your data model), you may want to use either the ROW_NUMBER or the DENSE_RANK analytic function rather than RANK.

User · Answer

If date and col date are the same columns you should simply do   SELECT A  MAX date  FROM t GROUP BY A   Why not use   WITH x AS   SELECT A  MAX col date  m FROM TABLENAME   SELECT A  date FROM TABLENAME t JOIN x ON x A   t A AND x m   t col date   Otherwise   SELECT A  FIRST VALUE date  KEEP dense rank FIRST ORDER BY col date DESC    FROM TABLENAME  GROUP BY A

User · Answer

Since Oracle 12C  you can fetch a specific number of rows with FETCH FIRST ROW ONLY  In your case this implies an ORDER BY  so the performance should be considered  SELECT A  col date FROM TABLENAME t ext ORDER BY col date DESC NULLS LAST FETCH FIRST 1 ROW ONLY   The NULLS LAST is just in case you may have null values in your field

User · Answer

Justin Cave answer is the best  but if you want antoher option  try this    select A col date from  select A col date     from tablename        order by col date desc        where rownum lt 2

User · Answer

A is the key  max date  is the value  we might simplify the query as below   SELECT distinct A  max date  over  partition by A    FROM TABLENAME

User · Answer

SELECT mu file  mudate   FROM flightdata t ext  WHERE mudate    SELECT MAX  mudate                       FROM flightdata where mudate  lt  sysdate

User · Answer

You could also use   SELECT t     FROM          TABLENAME t     JOIN           SELECT A  MAX col date  AS col date           FROM TABLENAME           GROUP BY A           m       ON  m A   t A       AND m col date   t col date

[oracle] Taking the record with the max date

Examples related to oracle

Examples related to date

Examples related to max

Examples related to analytic-functions