Select DISTINCT items rows and associated columns based on a condition

Question

Let's say I have a table like this in my DB

   Date   ItemId  Count
7/29/2010   3   1
7/30/2010   3   2
7/31/2010   2   3
7/29/2010   3   4
7/30/2010   1   5
7/31/2010   1   6
7/29/2010   2   7
7/30/2010   3   8
7/31/2010   1   2

For each item type, I want to select the count on the latest date found in the table only.

Is there an easy way to do this?

I'm thinking of doing something with DISTINCT to get the unique ItemIDs, but I'm stuck from there.

Answer 1

  SELECT ItemId, Count
  FROM
  ( SELECT Date, ItemId, Count, RANK() OVER (partition by ItemId, Order by ItemId, Date Desc) r
    FROM Table
  ) 
  WHERE r = 1