Retrieving Most Recent Sales Info

Question

SQL Server 2008R2 SP1

Let me preface by stating that I am a newbie to SQL Server with no formal training.

I have a table that includes (among other columns) CustNo, ItemNo, Amount, ShipDate, VersionTime

Any given customer will likely have more than one row for any given item number. I am trying to pull the information related to these fields for the most recent shipping date for every customer in this table. I am having a problem trying to exclude shipping dates older than the most current one. Obviously, customers will have most recent shipping dates that may or may not be different than the most recent shipping dates for a different customer and that is fine (I want the most recent ship date for each customer).

Information in the table might look like this:

CustNo, ItemNo, Amount, ShipDate, VersionTime

1, 12, 135, 1/1/2013, 1/2/2013

1, 6, 120, 1/1/2013, 1/2/2013

1, 6, 110, 1/1/2012, 2/1/2012

1, 5, 300, 1/1/2012, 2/1/2012

2, 12, 135, 2/1/2013, 2/3/2013

The data that I would like to be in the result set:

CustNo, ItemNo, Amount, ShipDate, VersionTime

1, 12, 135, 1/1/2013, 1/2/2013

1, 6, 120, 1/1/2013, 1/2/2013

2, 12, 135, 2/1/2013, 2/3/2013

Any help would be appreciated.

Answer 1

;With cte As
(Select CustNo, ItemNo, Amount, ShipDate, VersionTime,
  Rank() Over(Partition By CustNo Order By ShipDate Desc) As rk
From yourtable)
Select CustNo, ItemNo, Amount, ShipDate, VersionTime
From cte
Where rk = 1; 

Tom