1

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.

William Venice
  • 329
  • 2
  • 7
  • 16

3 Answers3

4
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.

今天春天
  • 941
  • 1
  • 13
  • 27
  • Is it any faster to do the inner select statement before-hand and storing it as a variable? Aren't we running the additional select for every single row in your example? There are 1 million+ records in this table. – William Venice Sep 23 '15 at 19:21
  • If your maximum manufactureDate changes very often, you might not gain any performance, but if it is rather a static value, you might be better off by redeclaring it beforehand. – 今天春天 Sep 23 '15 at 19:26
  • Do you know if declaring it beforehand even makes a difference or not? Part of me is assuming that sql goes out and does the query every single time anyway to set the variable. Meaning that your query has exactly the same performance as setting it beforehand. – William Venice Sep 23 '15 at 19:27
  • Maybe this helps you: http://stackoverflow.com/questions/23296223/where-case-clause-subquery-performance And YES, I also think that the subquery is executed for each and every row. – 今天春天 Sep 23 '15 at 19:33
  • 1
    If the subquery was referencing something in the outer query, then I think it would definitely have to run it for every row. As it stands now, SQL Server may be able to tell that there is no need to do so and it would only run it once. I doubt there's much performance difference between this and what you're doing now. You can always check the execution plan though. I would use the subquery just to avoid having to declare and assign the variable. – Mark Sponsler Sep 23 '15 at 19:47
0

You can use a subQuery 

 SELECT * 
 FROM Product 
 WHERE ManufactureDate = (
   SELECT ManufactureDate 
   FROM Product 
   ORDER BY ManufactureDate 
   LIMIT 1
 );`

You may need to use ASC or DESC to collect the right order

Seth McClaine
  • 9,142
  • 6
  • 38
  • 64
0

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.

Jaaz Cole
  • 3,119
  • 1
  • 15
  • 20