none
how to get last 3 purchase for each product in a subquery RRS feed

  • Question

  • PrdID PrdName purchase Date cost 1 I Pad 3.0 2012-03-19 190 1 I Pad 3.0 2012-03-28 200 2 I Pad 2.0 2012-01-26 150 2 I Pad 2.0 2012-03-19 160 3 I Phone 4.0 2012-01-24 400 3 I Phone 4.0 2012-03-28 420 4 I PHONE 4S 2012-01-24 450 4 I PHONE 4S 2012-01-26 460 5 Nokia N95 2012-03-29 100 6 Nokia 1800 2012-03-29 60

    Hello Readers. I am really stuck finding out the last three purchase  for each product depending up their purchase date. What basically i want to do is I need to calculate the avg cost for each product in their last 3 purchases in a subquery which i've found the most difficult thing ever seen up till now in my tsql life.


    I am desperately looking forward to your guidance. 


    Thanks In Advance.



    Hamad Salahuddin Elite Technologies,Abbottabad Pakistan www.et.com.pk


    Thursday, March 29, 2012 5:24 AM

Answers

  • WITH myCTE AS (
                      SELECT ROW_NUMBER() OVER(PARTITION BY PrdName ORDER BY PopurchaseDate DESC) AS 
                             RowNum
                             -- You can even use RANK() and DENSE_RANK() functions as applicable
                            ,p.PrdID
                            ,s.PrdName
                            ,a.PopurchaseDate
                            ,cost
                      FROM   MyTable
                  )
    SELECT *
          ,AVG(cost) OVER(PARTITION BY PrdName) AS Average
    FROM   myCTE
    WHERE  RowNum BETWEEN 1 AND 3
    

    Check this Link for the Usage of OVER clause and all aggregating functions used with the Over clause @ http://msdn.microsoft.com/en-us/library/ms189461.aspx


    Welcome to MSDN Forums.
    Feel free to ask your questions and Please Note to Vote all helpful topics and Mark all answering posts.

    You Value Community's efforts and Community values your faith.
    - Arun Kumar Allu

    • Proposed as answer by Naomi NModerator Friday, March 30, 2012 2:58 AM
    • Marked as answer by KJian_ Wednesday, April 4, 2012 3:11 AM
    Thursday, March 29, 2012 7:49 AM

All replies

  • Follow the OVER PARTITION BY example in the  article below:

    http://www.sqlusa.com/bestpractices2005/largestorder/

    You may decide to use a ranking function instead of ROW_NUMBER. When you use RANK as opposed to ROW_NUMBER, you may get more than 3 in a partition due to equal matches if any.


    Kalman Toth SQL SERVER & BI TRAINING




    Thursday, March 29, 2012 5:31 AM
    Moderator
  • WITH myCTE AS (
                      SELECT ROW_NUMBER() OVER(PARTITION BY PrdName ORDER BY PopurchaseDate DESC) AS 
                             RowNum
                             -- You can even use RANK() and DENSE_RANK() functions as applicable
                            ,p.PrdID
                            ,s.PrdName
                            ,a.PopurchaseDate
                            ,cost
                      FROM   MyTable
                  )
    SELECT *
          ,AVG(cost) OVER(PARTITION BY PrdName) AS Average
    FROM   myCTE
    WHERE  RowNum BETWEEN 1 AND 3
    

    Check this Link for the Usage of OVER clause and all aggregating functions used with the Over clause @ http://msdn.microsoft.com/en-us/library/ms189461.aspx


    Welcome to MSDN Forums.
    Feel free to ask your questions and Please Note to Vote all helpful topics and Mark all answering posts.

    You Value Community's efforts and Community values your faith.
    - Arun Kumar Allu

    • Proposed as answer by Naomi NModerator Friday, March 30, 2012 2:58 AM
    • Marked as answer by KJian_ Wednesday, April 4, 2012 3:11 AM
    Thursday, March 29, 2012 7:49 AM