select top 5 groups of row (group by based on date)[Answered] RSS

• • Reply
  uid571490

  Member

  102 Points

  247 Posts

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

  Jun 07, 2015 08:18 AM|uid571490|LINK

  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

• • Reply
  hans_v

  All-Star

  25756 Points

  7014 Posts

  Re: select top 5 groups of row (group by based on date)

  Jun 07, 2015 12:08 PM|hans_v|LINK

  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