locked
select top 5 groups of row (group by based on date) RRS feed

  • Question

  • User-1693623980 posted

    Hi,
    Consider we have a table for our Products like below:


    admin can add another product to this table dynamically.

    also we have another table like below that we save daily products prices in it: (Prices may change every day)

    the problem:

    I have to show last 5 days Prices like below:

    I could group by rows by date using this query:

    select top 5 DateOfPrice from(select DateOfPrice from tbl_Prices group by DateOfPrice order by DateOfPrice desc)

    but I dont know how can I fetch and show related fileds of each date, row by row, in a gridview.

    any Idea?
    Thanks

    Sunday, June 7, 2015 8:18 AM

Answers

  • User-1199946673 posted

    TRANSFORM First(Price)
    SELECT    DateOfPrice
    FROM      (SELECT     P.Title,
                          R.Price,
                          R.DateOfPrice
               FROM       tbl_Product AS P
               INNER JOIN tbl_Prices AS R ON P.ID = R.ProductID
               WHERE      R.DateOfPrice >= Date()-4)
    GROUP BY  DateOfPrice
    PIVOT     Title
    

    But if the amount of Products is increasing, the amount of columns will increase also. In Access, the amount of columns is limited to 255. So instead, you might consider this option?

    TRANSFORM First(Price)
    SELECT    Title
    FROM      (SELECT     P.Title,
                          R.Price,
                          R.DateOfPrice
               FROM       tbl_Product AS P
               INNER JOIN tbl_Prices AS R ON P.ID = R.ProductID
               WHERE      R.DateOfPrice >= Date()-4)
    GROUP BY  Title
    PIVOT     DateOfPrice
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, June 7, 2015 12:08 PM