what is the best way to have a SQL that only looks at MAX of a date field

i want to query a table where one column is run_date. I want to only retrieve results where run_date is the latest run_date in run_date column. is it possible to have a dynamic query like t开发者_运维百科his. something like:

Select * from Order where DateUpdated = Max(DateUpdated)

This will return you all results with the latest DateUpdated.

Select *
  from Order
 where DateUpdated = (SELECT Max(DateUpdated)
                        FROM Order )

If you need the most recent row:

select top 1 *
from Order
order by DateUpdated desc

If you need to retrieve all the rows that correspond to the most recent date, then you can do this efficiently with CTE:

with cte
as
(
    select *, rank() over(order by DateUpdated desc) RankNumber
    from [Order]
)
select *
from cte
where RankNumber = 1

Assuming MS SQL 2005 or higher.

If you use SQL 2000, then use zerkms has the answer.

try this query

Select * from Order where DateUpdated = (select Max(DateUpdated) from Order)