Posted by Tom Moreau on 04/20/07 14:08

Yep. What if the item returns to a location that it once occupied? Your
solution would not pick up the previous history.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"Robert Klemme" <shortcutter@googlemail.com> wrote in message
news:58rv9iF2ikhoqU1@mid.individual.net...
On 20.04.2007 15:49, markc600@hotmail.com wrote:
> If you are using SQL Server 2005, you can do this
>
> with cte(location_id,date_created,grp)
> as (
> select location_id,
> date_created,
> rank() over(partition by location_id order by date_created)
> - rank() over(order by date_created)
> from mytable)
> select location_id,
> max(date_created) as date_created
> from cte
> group by location_id,grp
> order by max(date_created)

I'd do

select location_id, max(date_created) date_created
from your_table
group by location_id
order by max(date_created)

Am I missing something?

robert

[Back to original message]