Search code examples
sqlsql-servermaxdate

Select multiple rows from a table where field is the max date

I have a table called Product. I need to select all product records that have the MAX ManufatureDate.

Here is a sample of the table data:

Id  ProductName     ManufactureDate
1   Car             01-01-2015
2   Truck           05-01-2015
3   Computer        05-01-2015
4   Phone           02-01-2015
5   Chair           03-01-2015

This is what the result should be since the max date of all the records is 05-01-2015 and these 2 records have this max date:

Id  ProductName     ManufactureDate
2   Truck           05-01-2015
3   Computer        05-01-2015

The only way I can think of doing this is by first doing a query on the entire table to find out what the max date is and then store it in a variable @MaxManufatureDate. Then do a second query where ManufactureDate=@MaxManufactureDate. Something tells me there is a better way.

There are 1 million+ records in this table:

Here is the way I am currently doing it:

@MaxManufactureDate = select max(ManufactureDate) from Product
select * from Product where ManufactureDate = @MaxManufactureDate

If figure this is a lot better then doing a subselect in a where clause. Or is this the same exact thing as doing a subselect in a where clause? I am not sure if the query gets ran for each row regardless or if sqlserver stored the variable value in memory.

Solution 
  • select * from product
where manufactureDate = (select max(manufactureDate) from product)

    The inner select-statements selects the maximum date, the outer all products which have the date.