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?
This question is related to
oracle
date
max
analytic-functions
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
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
SELECT mu_file, mudate
FROM flightdata t_ext
WHERE mudate = (SELECT MAX (mudate)
FROM flightdata where mudate < sysdate)
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.
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
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<2
Source: Stackoverflow.com