locked
Finding last purchase price RRS feed

  • Question

  • Hi,  I need to find the last purchase price for each product.  If I run the following code, I correctly get 1 result for each productID and the last purchase order number.

    SELECT pod.article as ProductID

    ,max(pod.order_ID) as LastOrder

    from apodetail pod

    group by pod.article

    order by pod.article


    Now I need to add in the price for that product on that orderID.  I've tried the following self join query, tried it without the join, and tried adding DISTINCT, but they all return more than 1 row per ProductID.

    SELECT pod.article as ProductID

    ,max(pod.order_ID) as LastOrder

    ,pod2.rev_price as UnitPrice

    from apodetail pod

    join

    apodetail pod2 on (pod2.order_ID = pod.order_id)

    group by pod.article,pod2.rev_price

    order by pod.article


    How can I get it to simply add the price to the first query?



    Tuesday, September 29, 2015 7:02 PM

Answers

  • Try it like this...

    WITH CTE AS (
    	SELECT 
    		pod.article AS ProductID,
    		pod.order_ID,
    		pod.rev_price,
    		ROW_NUMBER() OVER (PARTITION BY pod.article ORDER BY pod.order_ID DESC) AS rn
    	FROM 
    		dbo.apodetail pod
    )
    	SELECT 
    		c.ProductID,
    		c.order_ID,
    		c.rev_price
    	FROM 
    		CTE c
    	WHERE 
    		c.rn = 1


    Jason Long

    • Marked as answer by Philm778 Tuesday, September 29, 2015 10:53 PM
    Tuesday, September 29, 2015 9:10 PM

All replies

  • This is a very common problem and has been discussed here many times. You may find this blog post that provides several solutions helpful:

    Including an Aggregated Column’s Related Values


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, September 29, 2015 7:09 PM
  • Select ProductID, LastOrder, UnitPrice from  (
    SELECT pod.article as ProductID	
    	, pod.order_ID  as LastOrder	
    	,pod2.rev_price as UnitPrice,row_number() Over(Partition by pod.article  Order by pod.order_ID DESC) rn
    from apodetail pod join apodetail pod2	on  pod2.order_ID = pod.order_id ) t
    
    WHERE rn=1 
    order by ProductID

    Tuesday, September 29, 2015 7:13 PM
  • I figured it out.  I did this:

    select ProductID, LastOrder, pod2.rev_price from 
    (
    SELECT pod.article as ProductID
    	,max(pod.order_ID) as LastOrder
    from apodetail pod
    group by pod.article
    )
    z
    inner join
    apodetail pod2 on (pod2.order_ID = z.LastOrder and pod2.article = z.ProductID)
    order by ProductID

    Tuesday, September 29, 2015 7:14 PM
  • I figured it out.  I did this:

    select ProductID, LastOrder, pod2.rev_price from 
    (
    SELECT pod.article as ProductID
    	,max(pod.order_ID) as LastOrder
    from apodetail pod
    group by pod.article
    )
    z
    inner join
    apodetail pod2 on (pod2.order_ID = z.LastOrder and pod2.article = z.ProductID)
    order by ProductID

    Use the method that Jingyang provided. The method you came up with is going to have crap for performance compared to Jingyangs solution.

    Note that his solution only requires a single pass at the apodetail table... You're taking two... and you're joining on a calculated column. Not good. At least not as good as it could be.


    Jason Long

    Tuesday, September 29, 2015 8:15 PM
  • Jingyang,

    Thank you for this suggestion.  However, it doesn't give me the right UnitPrice.  The UnitPrice that it gives me on each product that I checked was never a price for that product at any time.  Not sure why that would be.

    I do appreciate how much more efficient your code would be if I could get it to give the right results.


    • Edited by Philm778 Tuesday, September 29, 2015 8:30 PM
    Tuesday, September 29, 2015 8:29 PM
  • Can you mock up some of your sample data to present the issue you are referring to ?

    Your table DDLs and a few rows of sample data for each table. Thanks.

    Tuesday, September 29, 2015 8:38 PM
  • I am not sure that ROW_NUMBER() approach outperforms INNER JOIN with derived table and GROUP BY. In various circumstances the performance may be different. Itzik Ben-Gan had a nice article a while ago comparing various methods of calculation. I'll try to find a reference.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, September 29, 2015 9:05 PM
  • Try it like this...

    WITH CTE AS (
    	SELECT 
    		pod.article AS ProductID,
    		pod.order_ID,
    		pod.rev_price,
    		ROW_NUMBER() OVER (PARTITION BY pod.article ORDER BY pod.order_ID DESC) AS rn
    	FROM 
    		dbo.apodetail pod
    )
    	SELECT 
    		c.ProductID,
    		c.order_ID,
    		c.rev_price
    	FROM 
    		CTE c
    	WHERE 
    		c.rn = 1


    Jason Long

    • Marked as answer by Philm778 Tuesday, September 29, 2015 10:53 PM
    Tuesday, September 29, 2015 9:10 PM
  • I did a quick google search, but could not find what I am looking for and need to run. Alejandro Mesa should have that link.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, September 29, 2015 9:14 PM
  • Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data (https://xkcd.com/1179/). We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL. 

    And you need to read and download the PDF for: https:--www.simple-talk.com-books-sql-books-119-sql-code-smells-

    You have data elements that change names from table to table! This is a huge design error that you need to fix.  Try this pattern to get the results. 

    WITH X 
    AS
    (SELECT product_id, order_id, ..
          MAX (order_id) OVER (PARTITION BY product_id)
          AS order_id_max
      FROM Apo_Details)

    SELECT * 
      FROM X
     WHERE order_id = order_id_max; 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Wednesday, September 30, 2015 2:08 AM