I am trying to select in a join/subquery the last time an item had an increased price.
this is somewhat of a challenge for me - can anyone lend a hand?
Ok, here are my takes, pretty similar. First one is valid if
And the second, which does away with that assumption by projecting a sequence over them:
answered Mar 26 '11 at 07:21 AM
Matt Whitfield ♦♦
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:
If you want the last time the price was increased then you may want something like:
Of course, in the sample data you provided, these will each return exactly the same results.
answered Mar 25 '11 at 12:55 PM
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:
will return the following:
If, on the other hand, you want the most recent set of prices, then you would replace the
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:
OK, so as you want the most recent price rise, then things are a little more complicated.
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.
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.
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
this query is nested and the standard for this company seems to be indexed temp tables vs cte's