T-SQL Subquery Max Date and Joins

Question

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

User · Answer

Something like this  SELECT    FROM MyParts  LEFT JOIN    SELECT MAX PriceDate   PartID FROM MyPrice group by PartID   myprice  ON MyParts Partid   MyPrice Partid    If you know your partid or can restrict it put it inside the join      SELECT myprice partid  myprice partdate  myprice2 Price         FROM MyParts      LEFT JOIN            SELECT MAX PriceDate   PartID FROM MyPrice group by PartID       myprice      ON MyParts Partid   MyPrice Partid      Inner Join MyPrice myprice2     on myprice2 pricedate   myprice pricedate     and myprice2 partid   myprice partid

User · Answer

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  gt  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

User · Answer

SELECT       FROM      SELECT MAX PriceDate  AS MaxP  Partid FROM MyPrices GROUP BY Partid  MaxP      JOIN     MyPrices MP On MaxP Partid   MP Partid AND MaxP MaxP   MP PriceDate     JOIN     MyParts P ON MP Partid   P Partid   You to get the latest pricedate for partid first  a standard aggregate   then join it back to get the prices  which can t be in the aggregate   followed by getting the part details

User · Answer

SELECT     MyParts   MyPriceDate Price MyPriceDate PriceDate     FROM MyParts         INNER JOIN  SELECT Partid  MAX PriceDate  AS MaxPriceDate FROM MyPrice GROUP BY Partid  dt ON MyParts Partid   dt Partid         INNER JOIN MyPrice ON dt Partid   MyPrice Partid AND MyPrice PriceDate dt MaxPriceDate

User · Answer

Join on the prices table  and then select the entry for the last day   select pa partid  pa Partnumber  max pr price  from myparts pa inner join myprices pr on pr partid   pa partid where pr PriceDate         select max PriceDate       from myprices      where partid   pa partid     The max   is in case there are multiple prices per day  I m assuming you d like to display the highest one   If your price table has an id column  you can avoid the max   and simplify like   select pa partid  pa Partnumber  pr price from myparts pa inner join myprices pr on pr partid   pa partid where pr priceid         select max priceid      from myprices      where partid   pa partid     P S  Use wcm s solution instead

User · Answer

All other answers must work  but using your same syntax  and understanding why the error   SELECT   FROM MyParts LEFT JOIN MyPrice ON MyParts Partid   MyPrice Partid WHERE  MyPart PriceDate    SELECT MAX MyPrice2 PriceDate  FROM MyPrice as MyPrice2  WHERE MyPrice2 Partid    MyParts Partid

User · Answer

Try this   Select        Price    Select top 1 Price               From MyPrices               where PartID   mp PartID               order by PriceDate desc               from MyParts mp

User · Answer

Please try next code example   select t1    t2 partprice  t2 partdate  from myparts t1 join myprices t2  on t1 partid   t2 partid where partdate     select max partdate  from myprices t3  where t3 partid   t2 partid group by partid

User · Answer

For MySQL  please find the below query   select   from  select PartID  max Pricedate  max pricedate from MyPrices group bu partid  as a      inner join MyParts b on      a partid   b partid and a max pricedate   b pricedate    Inside the subquery it fetches max pricedate for every partyid of MyPrices then  inner joining with MyParts using partid and the max pricedate

User · Answer

In SQL Server 2005 and later use ROW NUMBER    SELECT   FROM        SELECT p            ROW NUMBER   OVER PARTITION BY Partid ORDER BY PriceDate DESC  AS rn     FROM MyPrice AS p   AS t WHERE rn 1

[sql] T-SQL Subquery Max(Date) and Joins

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to join

Examples related to subquery