Posted by Anthony Paul on 04/20/07 14:22

Hello Mark,

So fast on the trigger! What do you do, have a notification bell ring
whenever something is posted on this newsgroup? :P

Your solution worked perfectly... I'm used to regular ol' SQL and what
you've just shown me is amazing; I didn't know SQL had these
capabilities. rank, over and partition are new to me. Do you have any
book recommendations?

Regards,

Anthony

On Apr 20, 9:49 am, markc...@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)

[Back to original message]