Oracle select most recent date record

Question

I am trying to find the most recent record based on a date field   When I set latest   1 in the where clause  I get an error   Please help if possible  DATE is a the field I m sorting by  I have tried both latest   1 and latest    1   SELECT  STAFF ID  SITE ID  PAY LEVEL  ROW NUMBER   OVER  PARTITION BY STAFF ID ORDER BY DATE DESC  latest   FROM OWNER TABLE WHERE   END ENROLLMENT DATE is null  AND latest   1

User · Accepted Answer

you can t use aliases from select list inside the WHERE clause  because of the Order of Evaluation of a SELECT statement   also you cannot use OVER clause inside WHERE clause -  You can specify analytic functions with this clause in the select list or ORDER BY clause    citation from docs oracle com   select   from  select   staff id  site id  pay level  date     max date  over  partition by staff id  max date   from owner table   where end enrollment date is null   where date   max date

User · Answer

i think i d try with MAX something like this   SELECT staff id  max  date   from owner table group by staff id   then link in your other columns   select staff id  site id  pay level  latest from owner table       SELECT staff id  max  date   latest from owner table group by staff id   m where m staff id   staff id and m latest   date

User · Answer

select   from  select   staff id  site id  pay level  date     rank   over  partition by staff id order by date desc  r   from owner table   where end enrollment date is null   where r   1

User · Answer

Assuming staff id   date form a uk  this is another method   SELECT STAFF ID  SITE ID  PAY LEVEL   FROM TABLE t   WHERE END ENROLLMENT DATE is null     AND DATE    SELECT MAX DATE                    FROM TABLE                   WHERE staff id   t staff id                     AND DATE  lt   SYSDATE

[oracle] Oracle select most recent date record

Examples related to oracle