Is there a better way to write this SQL query?

Question

I'm looking to return the row with biggest create_dt. This works fine, however I would like to know if there is a more proper way of doing this?

select * from 
table1 
where job_no='101047' 
and 
create_dt in
     (select max(create_dt) from    
      table1 where job_no='101047')

Answer 1

How about:

Select top 1 *
from table1
where job_no = '101047'
order by create_dt desc

Answer 2

your query will return more than one value if there is more than one row of create_dt
where job_no = '101047'

This will work better

 Select top 1 * from table1 
 where job_no='101047'   
 order by create_dt desc