Combining Records On Secondary Grouping-CodePudding

I have a table of speed limits, road numbers, and road segments. Each road may have multiple segments within a speed limit segment (same road number, same speed limit, different bmp and emp segments). Each road may change speed limit (same road, different speed limit, starts a new bmp). I need to group the roads by speed limit, but collapse any segments of the same road into 1 unit where the speed limit is the same. In the following return, for example, records 2 and 3 should be collapsed into 1 record, because the road has the same speed limit, even though that speed limit crosses multiple segments (0.0 - 10.5).
Sorry: The bmp and emp are "Beginning Mile Post" and "End Mile Post" and separate the road into the afore mentioned segments.

           Limit     RoadNum    bmp     emp
     1     25        10001      9.06    16.90
     2     25        09002      0.00     9.39
     3     25        09002      9.20    10.50
     4     25        08003      0.00     3.10

The output should look like this (where rec 2 has its original bmp, but the emp of rec 3) Note: record number 1...4 is not a key or even part of the table.

           Limit     RoadNum    bmp     emp
     1     25        10001      9.06    16.90
     2     25        09002      0.00    10.50
     4     25        08003      0.00     3.10

This is the simple select statement I used to produce the grouping:

select Limit, RoadNum, BMP, EMP
from SpeedLimits
group by Limit, RoadNum, BMP, EMP
order by Limit asc

CodePudding user response：

Looks like what you need is to GROUP BY Limit and RoadNum and find MIN(BMP) and MAX(EMP)

select Limit, RoadNum, BMP = MIN(BMP), EMP = MAX(EMP)
from   SpeedLimits
group by Limit, RoadNum
order by Limit asc