2

I have an Oracle table with two columns ID and START_DATE, I want to run a query to get the ID of the record with the most recent date, initially i wrote this:

 select id from (select * from mytable order by start_date desc) where rownum = 1

Is there a more cleaner and efficient way of doing this? I often run into this pattern in SQL and end up creating a nested query.

wsb3383
  • 3,841
  • 12
  • 44
  • 59

2 Answers2

1
SELECT id FROM mytable WHERE start_date = (SELECT MAX(start_date) FROM mytable)

Still a nested query, but more straightforward and also, in my experience, more standard.

froadie
  • 79,995
  • 75
  • 166
  • 235
  • Better, but I think OP wanted to avoid nested queries. I would have tried something with analytic functions... but not sure that would work... hmm... – FrustratedWithFormsDesigner Aug 20 '10 at 19:01
  • 1
    This fulfils the 'get all records with the most recent start date', which the OPs query doesn't. +1 – Will A Aug 20 '10 at 19:02
  • Thanks! I'm not really trying to avoid nested queries, I'm very new to SQL so I just wanted to know if what i'm doing is common (or bad) sql coding practice. – wsb3383 Aug 20 '10 at 19:10
0

This looks to be a pretty clean and efficient solution to me - I don't think you can get any better than that, of course assuming that you've an index on start_date. If you want all ids for the latest start date then froadie's solution is better.

Will A
  • 24,780
  • 5
  • 50
  • 61