Posted by Greg D. Moore \(Strider\) on 04/21/07 02:56

"Anthony Paul" <anthonypaulo@gmail.com> wrote in message
news:1177078960.725463.69600@y80g2000hsf.googlegroups.com...
> Hello Mark,
>
> So fast on the trigger! What do you do, have a notification bell ring
> whenever something is posted on this newsgroup? :P
>

I can't speak for Mark, but some of us have way too much time on our hands.
;-)


> 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?

Look for books by Itzak Ben-Gan. (Inside Microsoft SQL Server 2005; T-SQL
Querying covers this. it's a island/gap problem.)

I remember the first time I saw him demo those functions (they're new in SQL
2005).

Some pretty incredible stuff.


>
> 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)
>
>

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

[Back to original message]