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

www.insidetsql.com


--
Tom

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


"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

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)

• Next in forum: Re: Filter records during Transactional Replication.
• Prev in forum: Re: Interesting SQL problem : How to track movement history
• Thread view: Re: Interesting SQL problem : How to track movement history

[Reply to this message]