Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • World
  • Users
  • Groups
Skins
  • Light
  • Cerulean
  • Cosmo
  • Flatly
  • Journal
  • Litera
  • Lumen
  • Lux
  • Materia
  • Minty
  • Morph
  • Pulse
  • Sandstone
  • Simplex
  • Sketchy
  • Spacelab
  • United
  • Yeti
  • Zephyr
  • Dark
  • Cyborg
  • Darkly
  • Quartz
  • Slate
  • Solar
  • Superhero
  • Vapor

  • Default (No Skin)
  • No Skin
Collapse
Code Project
  1. Home
  2. Database & SysAdmin
  3. Database
  4. Grouping by most recent date

Grouping by most recent date

Scheduled Pinned Locked Moved Database
databasequestionlearning
4 Posts 2 Posters 0 Views 1 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • N Offline
    N Offline
    Netblue
    wrote on last edited by
    #1

    I have a table called CostAverage which stores the average value of an item and the date that it was calculated on. Each item has its cost average calculated on different days, and only when changes are made to the item. Since the OnDate could be anything, and the table retains the history, I am looking for a way to get the most recent cost average for each item in the table. Here is a snapshot of the table: CostAverage PK CostAverageID FK UserItemID CostAverage OnDate At first glance, I threw down something like this: SELECT UserItemID, CostAverage, MAX(OnDate) FROM CostAverage GROUP BY UserItemID, CostAverage Since the CostAverage is always different, that of course returns nearly the entire table. I can't really use an aggregate on the CostAverage since I need the most recent. Does anyone have a suggestion? I am sure that I wrote a similar query once in the past, but I can't seem to find it (or remember it)

    A 1 Reply Last reply
    0
    • N Netblue

      I have a table called CostAverage which stores the average value of an item and the date that it was calculated on. Each item has its cost average calculated on different days, and only when changes are made to the item. Since the OnDate could be anything, and the table retains the history, I am looking for a way to get the most recent cost average for each item in the table. Here is a snapshot of the table: CostAverage PK CostAverageID FK UserItemID CostAverage OnDate At first glance, I threw down something like this: SELECT UserItemID, CostAverage, MAX(OnDate) FROM CostAverage GROUP BY UserItemID, CostAverage Since the CostAverage is always different, that of course returns nearly the entire table. I can't really use an aggregate on the CostAverage since I need the most recent. Does anyone have a suggestion? I am sure that I wrote a similar query once in the past, but I can't seem to find it (or remember it)

      A Offline
      A Offline
      Ashfield
      wrote on last edited by
      #2

      Try this

      SELECT UserItemID, CostAverage,OnDate
      FROM CostAverage ca1
      where OnDate = (SELECT MAX(OnDate)
      FROM CostAverage ca2 where ca2.UserItemID = ca1.UserItemID)

      Personally, I would add a flag to indicate the current record and maintain it via a trigger as the above may be slow depending on your indexing.

      Bob Ashfield Consultants Ltd

      N 1 Reply Last reply
      0
      • A Ashfield

        Try this

        SELECT UserItemID, CostAverage,OnDate
        FROM CostAverage ca1
        where OnDate = (SELECT MAX(OnDate)
        FROM CostAverage ca2 where ca2.UserItemID = ca1.UserItemID)

        Personally, I would add a flag to indicate the current record and maintain it via a trigger as the above may be slow depending on your indexing.

        Bob Ashfield Consultants Ltd

        N Offline
        N Offline
        Netblue
        wrote on last edited by
        #3

        Thank you, that works quite well. The query runs around ~2 seconds, this table should be weeded out in the next 6 months during a refactor so I'm not too worried about performance yet. Again, thanks.

        A 1 Reply Last reply
        0
        • N Netblue

          Thank you, that works quite well. The query runs around ~2 seconds, this table should be weeded out in the next 6 months during a refactor so I'm not too worried about performance yet. Again, thanks.

          A Offline
          A Offline
          Ashfield
          wrote on last edited by
          #4

          No problem

          Bob Ashfield Consultants Ltd

          1 Reply Last reply
          0
          Reply
          • Reply as topic
          Log in to reply
          • Oldest to Newest
          • Newest to Oldest
          • Most Votes


          • Login

          • Don't have an account? Register

          • Login or register to search.
          • First post
            Last post
          0
          • Categories
          • Recent
          • Tags
          • Popular
          • World
          • Users
          • Groups