Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

T-SQL Subquery Max(Date) and Joins

I'm trying to join multiple tables, but one of the tables has multiple records for a partid with different dates. I want to get the record with the most recent date.

Here are some example tables:

Table: MyParts Partid   Partnumber   Description 1        ABC-123      Pipe 2        ABC-124      Handle 3        ABC-125      Light   Table: MyPrices Partid   Price        PriceDate 1        $1           1/1/2005 1        $2           1/1/2007 1        $3           1/1/2009 2        $2           1/1/2005 2        $4           1/1/2006 2        $5           1/1/2008 3        $10          1/1/2008 3        $12          1/1/2009 

If I was just wanted to find the most recent price for a certain part I could do:

SELECT * FROM MyPrice WHERE PriceDate = (SELECT MAX(PriceDate)  FROM MyPrice WHERE Partid = 1) 

However I want to do a join first and get back the correct price for all parts not just one. This is what I have tried:

SELECT * FROM MyParts LEFT JOIN MyPrice ON MyParts.Partid = MyPrice.Partid WHERE  MyPart.PriceDate = (SELECT MAX(PriceDate) FROM MyPrice) 

The results are wrong as it takes the highest price date of the entire table.

SELECT * FROM MyParts LEFT JOIN MyPrice ON MyParts.Partid = MyPrice.Partid WHERE  MyPart.PriceDate = (SELECT MAX(PriceDate) FROM MyPrice WHERE MyPrice.Partid =    MyParts.Partid) 

That errors out.

What can I do to get the results I want.

like image 985
MaxGeek Avatar asked May 18 '09 18:05

MaxGeek


People also ask

Why CTE is better than subquery?

CTE can be more readable: Another advantage of CTE is CTE are more readable than Subqueries. Since CTE can be reusable, you can write less code using CTE than using subquery. Also, people tend to follow the logic and ideas easier in sequence than in a nested fashion.

Which is faster subquery or joins?

Advantages Of Joins: The retrieval time of the query using joins almost always will be faster than that of a subquery. By using joins, you can maximize the calculation burden on the database i.e., instead of multiple queries using one join query.

How is CTE different from subquery?

CTE or COMMON TABLE EXPRESSION — a type of temporary data source that houses the results of a query. CTEs are only stored for the duration of a query. SUBQUERY — just like CTEs and temp tables, a subquery is a way to generate a temporary result set to use in a main query.


1 Answers

Here's another way to do it without subqueries. This method will often outperform others, so it's worth testing both methods to see which gives the best performance.

SELECT      PRT.PartID,      PRT.PartNumber,      PRT.Description,      PRC1.Price,      PRC1.PriceDate FROM      MyParts PRT LEFT OUTER JOIN MyPrices PRC1 ON      PRC1.PartID = PRT.PartID LEFT OUTER JOIN MyPrices PRC2 ON      PRC2.PartID = PRC1.PartID AND      PRC2.PriceDate > PRC1.PriceDate WHERE      PRC2.PartID IS NULL 

This will give multiple results if you have two prices with the same EXACT PriceDate (Most other solutions will do the same). Also, I there is nothing to account for the last price date being in the future. You may want to consider a check for that regardless of which method you end up using.

like image 52
Tom H Avatar answered Sep 20 '22 10:09

Tom H