Subbquery Maximum Dates

Ok, here are my takes, pretty similar. First one is valid if prc_eff_dt is always 1 day after the last prc_end_dt for the same item:

SELECT [priceRises].[item], MAX([prc_eff_dt]) AS LastPriceRiseDate FROM (
    SELECT [newPrices].[item],
           [newPrices].[prc_eff_dt]
      FROM #temp AS [newPrices] LEFT OUTER JOIN
           #temp AS [oldPrices]
        ON [oldPrices].[item] = [newPrices].[item]
       AND [oldPrices].[prc_end_dt] = DATEADD(d, -1, [newPrices].[prc_eff_dt])
     WHERE [newPrices].[price] > [oldPrices].[price]
) priceRises
GROUP BY [priceRises].[item];

And the second, which does away with that assumption by projecting a sequence over them:

WITH priceSequence (item, price, effectiveDate, sequenceNumber) AS
(SELECT [item], [price], [prc_eff_dt], ROW_NUMBER() OVER (PARTITION BY [item] ORDER BY [prc_eff_dt]) FROM #temp)
SELECT [priceRises].[item], MAX([effectiveDate]) AS LastPriceRiseDate FROM (
    SELECT [newPrices].[item],
           [newPrices].[effectiveDate]
      FROM priceSequence AS [newPrices] LEFT OUTER JOIN
           priceSequence AS [oldPrices]
        ON [oldPrices].[item] = [newPrices].[item]
       AND [oldPrices].[sequenceNumber] = [newPrices].[sequenceNumber] - 1
     WHERE [newPrices].[price] > [oldPrices].[price]
) priceRises
GROUP BY [priceRises].[item];

Your title and your actual question seem to be a little at odds. If you want the row with the highest price for that item, then this should work:

select * 
from #temp t1
where not exists
(select * 
from #temp t2
where t1.item = t2.item
and t1.price < t2.price)

If you want the last time the price was increased then you may want something like:

with cte1 as (
--This will return every time there is a price increase

    select * 
    from #temp t1
    where exists 
    --make sure this is an increase
    --so the very last one to happen has to 
    (
       select * 
       from #temp t2
       where t1.item = t2.item
       and t1.price > t2.price
       and t1.prc_eff_dt > t2.prc_eff_dt 
       --can't be anything in between
       and not exists 
       (
         select *
         from #temp t3
         where t3.item = t1.item
         and t3.prc_eff_dt  < t1.prc_eff_dt  and 
          t3.prc_eff_dt  > t2.prc_eff_dt 
          )
    )
)

--This gets the latest price increase
select * 
from cte1 t1
where not exists
(select * 
from cte1 t2
where t1.item = t2.item
and t1.prc_eff_dt < t2.prc_eff_dt)

Of course, in the sample data you provided, these will each return exactly the same results.

This seems to me to be the sort of thing where a nice handy ROW_NUMBER partitioned function would come in handy:

Replace your SELECT statement with the following:

;WITH OrderedPrice AS (    
SELECT item, price, prc_eff_dt, prc_end_dt, 
       ROW_NUMBER() OVER(PARTITION BY item ORDER BY price desc) AS RowNum
FROM #temp)
SELECT item, price, prc_eff_dt, prc_end_dt FROM OrderedPrice WHERE RowNum = 1

will return the following:

item   price  prc_eff_dt               prc_end_dt
1234   15.00  2009-02-10 00:00:00.000  2009-02-17 00:00:00.000
5678   15.00  2009-02-10 00:00:00.000  2009-02-17 00:00:00.000

If, on the other hand, you want the most recent set of prices, then you would replace the

...PARTITION BY item ORDER BY price DESC...

with

....PARTITION BY item ORDER BY prc_eff_dt DESC...

oh, and if you want the most recent maximum price (in case something has had two goes at the highest price), then you would do:

....PARTITION BY item ORDER BY price DESC, prc_eff_dt DESC...

OK, so as you want the most recent price rise, then things are a little more complicated.

;WITH OrderedPrice AS (    
SELECT item, price, prc_eff_dt, prc_end_dt, ROW_NUMBER() OVER(PARTITION by item ORDER BY prc_eff_dt desc) AS RowNum
FROM #temp),
PriceHikes AS (
SELECT op1.item, op1.price, op1.prc_eff_dt, op1.prc_end_dt, ROW_NUMBER() OVER(PARTITION BY op1.item ORDER BY op1.prc_eff_dt DESC) AS RowNum
FROM OrderedPrice op1 LEFT JOIN OrderedPrice op2 ON op1.item = op2.item AND op1.prc_eff_dt = DATEADD(DAY, 1, op2.prc_end_dt)
WHERE op1.price > op2.price
)
SELECT item, price, prc_eff_dt, prc_end_dt
FROM [PriceHikes]
WHERE RowNum = 1

NB: Any errors are entirely due to lack of sleep; however, I believe this does show the way forward for your problem...

Also, it's worth noting that given your sample data, this returns the same set of records as the query that returns the highest prices...

This will give you the record for the highest price, but that doesn't mean that it's the last time the price was raised.

;with cte
AS
(
    select 
    item,
    maxPrice = MAX(Price)
    from #temp
    group by item
)
select t.* from cte c
inner join #temp t
on c.item=t.item
and c.maxPrice=t.price

The second example will give you the last time the price was updated, but I am not sure it's the most efficient, but at least it's not using any cursor.

;with cte1
AS
(
    select 
    *,
    rownum = row_number() over(partition by item order by prc_eff_dt)
    from #temp
), cte2
as(
select t.item, maxrownum= max(t.rownum) from cte1 c
inner join cte1 t
    on c.item = t.item
    and c.rownum +1= t.rownum 
    where c.price <t.price
group by t.item
)
select c1.* from cte1 c1 inner join cte2 c2
on c1.item=c2.item
and c1.rownum=c2.maxrownum

Hakan - you are correct - i do need the last time the price was raised, not necessarily the max price.

the other challenge i am facing in this example - i can have multiple date entries with the same price. so

item prc date 
1234 10 1/1/10 
1234 11 1/2/10 
1234 11 1/9/10 
1234 11 1/31/10 

this query is nested and the standard for this company seems to be indexed temp tables vs cte's