CODEWITHSUNDEEP
sql-servert-sql
jr3
jr3

Reputation: 915

Is there a better way to write this SQL query?

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')

Upvotes: 3

Views: 242

Answers (2)

Dusty
Dusty

Reputation: 4697

How about:

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

Upvotes: 15

Johnno Nolan
Johnno Nolan

Reputation: 29659

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

Upvotes: 4

Related Questions