1

I want to select the latest record from a table as based on a date field (crtn_dt). The query below does not work. Does anyone have an idea how it should be fixed?

 select * from parcels
 order by crtn_dt desc
 where rownum = 1
Patty Jula
  • 255
  • 1
  • 12
  • 2
    the answer here may help: https://stackoverflow.com/questions/11128194/oracle-select-most-recent-date-record – Kevin M Oct 10 '17 at 16:02
  • well syntax is wrong on your query. where comes after from before order by. but then it still will not produce desired results. – xQbert Oct 10 '17 at 16:05

1 Answers1

1

You'd need to order data in the subquery and filter them in an outer query.

select *
  from (
        select * 
          from parcels
         order by crtn_dt desc
       )
 where rownum = 1

order by clause is among last operations to perform. What your query does, apart from being semantically incorrect, it returns one (thanks to rownum = 1 predicate) arbitrary row, and then applies order by clause to that one row.

Nick Krasnov
  • 26,886
  • 6
  • 61
  • 78