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 · 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

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

User · Answer

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

User · 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

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

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

[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