Question: 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.

Answer: 
```
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.
Try this pattern:

```
SELECT Id, ProductName, ManufactureDate
FROM (
    SELECT Id, ProductName, ManufactureDate, MAX(ManufactureDate)OVER() AS MaxManufactureDate
    FROM Product P
    ) P
WHERE P.MaxManufactureDate = P.ManufactureDate

```

Essentially, use a window function to get the data you're looking for in the inline view, then use the where clause in the outer query to match them.