Retrieve rows having most recent date for condition

I am trying to retrieve a set of Rows from a Sales Price table, for specified currencies. I am interested in only the most recently added rows for each of the Items in this table, for each currency.

Using one Item as an example, I can set up a query like this:

SELECT [Item No_],MAX([Starting Date])as date
FROM [dbo].[Maglin Furniture Systems Ltd_$Sales Price]
WHERE [Item No_]='I002151'
AND [Currency Code]='USD'
GROUP By [Item No_]

which gives me this result:

Item No.   date
I002151    2010-12-04 00:00:00:0000

For this Item, (I002151) there are several other rows as returned by this query:

SELECT [Item No_],[Starting Date]
FROM [dbo].[Maglin Furniture Systems Ltd_$Sales Price]
WHERE [Item No_]='I002151'
AND [Currency Code]='USD'

which gives me


Item No_      Starting Date
I002151      2004-01-01 00:00:00.000
I002151      2005-01-01 00:00:00.000
I002151      2007-05-03 00:00:00.000
I002151      2007-11-09 00:00:00.000
I002151      2010-12-04 00:00:00.000


What I want though is a result like this:

Item No_      Unit Price                                         Starting Date

I002151      895.00000000000000000000      2010-12-04 00:00:00.000


So, I think, I can just do this, right?

SELECT [Item No_],[Unit Price],MAX([Starting Date])as date
FROM [dbo].[Maglin Furniture Systems Ltd_$Sales Price]
WHERE [Item No_]='I002151'
AND [Currency Code]='USD'
GROUP By [Item No_],[Unit Price]

Wrong!

THis query gives me this result:

Item No_      Unit Price                                          date
I002151      695.00000000000000000000      2007-05-03 00:00:00.000
I002151      795.00000000000000000000      2007-11-09 00:00:00.000
I002151      855.00000000000000000000      2005-01-01 00:00:00.000
I002151      895.00000000000000000000      2010-12-04 00:00:00.000


I've looked around on the Net to find a solution but none of these gives me what I am looking for.

Ultimately, I want to end up with two result sets that give me the most recently entered price (Unit Cost) for each Item (plus any other columns I want to include in each row), one set for one currency and one set for another.

Is there a way to do this?
MaglinFurnitureAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
SELECT [Item No_],[Unit Price],MAX([Starting Date])as date
FROM [dbo].[Maglin Furniture Systems Ltd_$Sales Price]
WHERE [Item No_]='I002151'
AND [Currency Code]='USD'
AND [Starting Date] =MAX([Starting Date])
GROUP By [Item No_],[Unit Price]
0
MaglinFurnitureAuthor Commented:
@Icohan

If I use your query, I get this error message:

Msg 147, Level 15, State 1, Line 5
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
0
lcohanDatabase AnalystCommented:
Argh....sorry about that - lets move it to HAVING then like:

SELECT [Item No_],[Unit Price],MAX([Starting Date])as date
FROM [dbo].[Maglin Furniture Systems Ltd_$Sales Price]
WHERE [Item No_]='I002151'
AND [Currency Code]='USD'
GROUP By [Item No_],[Unit Price]
HAVING [Starting Date] =MAX([Starting Date])
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

MaglinFurnitureAuthor Commented:
With this query, I get thiserror message:

Msg 8121, Level 16, State 1, Line 7
Column 'dbo.Maglin Furniture Systems Ltd_$Sales Price.Starting Date' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.


If I add [Starting Date] to GROUP BY,


SELECT [Item No_],[Unit Price],MAX([Starting Date])as date
 FROM [dbo].[Maglin Furniture Systems Ltd_$Sales Price]
WHERE [Item No_]='I002151'
 AND [Currency Code]='USD'
 GROUP By [Item No_],[Unit Price],[Starting Date]
 HAVING [Starting Date] =MAX([Starting Date])

 I get this result:

Item No_      Unit Price                          date
I002151      695.00000000000000000000      2004-01-01 00:00:00.000
I002151      855.00000000000000000000      2005-01-01 00:00:00.000
I002151      695.00000000000000000000      2007-05-03 00:00:00.000
I002151      795.00000000000000000000      2007-11-09 00:00:00.000
I002151      895.00000000000000000000      2010-12-04 00:00:00.000


instead of

Item No_      Unit Price                          date
I002151      895.00000000000000000000      2010-12-04 00:00:00.000
0
lcohanDatabase AnalystCommented:
SELECT [Item No_],
      (SELECT [Unit Price] FROM [dbo].[Maglin Furniture Systems Ltd_$Sales Price] where [Starting Date] = MAX([Starting Date])) as Unit_Price,
      MAX([Starting Date])as date
FROM [dbo].[Maglin Furniture Systems Ltd_$Sales Price]
WHERE [Item No_]='I002151'
AND [Currency Code]='USD'
AND [Starting Date] = MAX([Starting Date])
GROUP By [Item No_],[Unit Price]
HAVING [Starting Date] = MAX([Starting Date])
0
MaglinFurnitureAuthor Commented:
This one gives me this error:

Msg 147, Level 15, State 1, Line 7
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
0
PortletPaulfreelancerCommented:
Try using ROW_NUMBER() in a derived table:
SELECT
      [Item No_]
    , [Unit Price]
    , [Starting Date]
FROM (
      SELECT
            [Item No_]
          , [Unit Price]
          , [Starting Date]
          , ROW_NUMBER() OVER (PARTITION BY [Item No_]
                               ORDER BY [Starting Date] DESC) AS rn
      FROM [dbo].[Maglin Furniture Systems Ltd_$Sales Price]
      WHERE [Item No_] = 'I002151'
            AND [Currency Code] = 'USD'
) AS derived
WHERE rn = 1

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MaglinFurnitureAuthor Commented:
@Paul Maxwell

Wow, what a query!

I have no idea what is going on with this one (not completely), but, it works!
0
PortletPaulfreelancerCommented:
, ROW_NUMBER() OVER (PARTITION BY [Item No_]
                               ORDER BY [Starting Date] DESC) AS rn

The real magic is in the OVER clause which has 2 parts:

"partition by" - which is a little similar to "group by" - here this instructs the row_number function restarts at 1 for each unique value  of [Item No_]

"order by" is the sequence that row_number will increment. In this case by date DESCending so the combined "partition" and "order" places the value 1 as the latest date for each [Item No_]

ROW_NUMBER() increments by 1 for each row as determined by parameters of the OVER clause.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.