Aggregate values based on distance field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Aggregate values based on distance field

Author

Topic

JacekK
Starting Member

14 Posts

Posted - 2010-06-16 : 11:03:13

I am a novice in sql programming and (of course) I need help with grouping speed values based on the distance (mile post) field. I can do this using VB and probably the same methods can be implemented in sql using cursors, but, if possible, I want to avoid cursors for now.
Here is an example of my table:

Speed RteID Mile Post
60 94E 0
60 94E 15000
75 94E 25000
75 94E 30000
60 94E 40000
60 94E 70000
75 94E 85000

Here is an example of what I need:

Speed RteID Mile Post
60 94E 0
75 94E 25000
60 94E 40000

Any help appreciated.
Thanks!
Jack

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-16 : 11:47:42

Sorry I don't see a rule to get this output.
Can you clarify please?

No, you're never too old to Yak'n'Roll if you're too young to die.

JacekK
Starting Member

14 Posts

Posted - 2010-06-16 : 11:55:11

I apologize for not being clear. I have a table that lists speed limits on the segments of the highway. These are broken down by mileposts on the highway. However, these speed limits were updated and I need to aggregate them, i.e., in a table above there is a speed limit of 60 miles on Route 94E from milepost 0 to milepost 25000. Instead of 2 records for this I need to show only 1 record.
Is this any clearer???

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-16 : 13:56:19

Shouldn't

75 94E 85000

also be in your result?
Try this
Sample Data

declare @t table
(Speed int,RteID varchar(10), MilePost int)
insert @t
select 60, '94E', 0
union all select 60, '94E', 15000
union all select 75, '94E', 25000
union all select 75, '94E', 30000
union all select 60, '94E', 40000
union all select 60, '94E', 70000
union all select 75, '94E', 85000

Query

select a.* from
(
select row_number() over(partition by RteID Order by MilePost) as seq, * from @t
) a
left join 
(
select row_number() over(partition by RteID Order by MilePost) as seq, * from @t
) b
on a.RteID = b.RteID and a.seq = b.seq + 1 and a.Speed = b.Speed
where b.RteID is NULL

JacekK
Starting Member

14 Posts

Posted - 2010-06-16 : 14:15:45

That's it! Thanks a lot! I didn't even know there is a "partition" command. Lots to learn...

Vijayisonly you are a genius!

Jack

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-16 : 14:47:04

You are welcome.

JacekK
Starting Member

14 Posts

Posted - 2010-06-17 : 12:33:02

I was trying to dissect your code (I must admit, it was not as easy as it may seem), and I came up with another code that I believe is doing the same thing. I still have to test this on the large table...

declare @t table
(Speed int,RteID varchar(10), MilePost int)
insert @t
select 60, '94E', 0
union all select 60, '94E', 15000
union all select 75, '94E', 25000
union all select 75, '94E', 30000
union all select 60, '94E', 40000
union all select 60, '94E', 70000
union all select 75, '94E', 85000

select speed,rteid,min(milepost) as milepost
from
(
select
*,
rank() over (partition by rteid,speed order by milepost) -
rank() over (partition by rteid order by milepost) as rank2
from
@t
) as a
group by
rteid,speed,rank2
order by rteid,MilePost

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources