SQL Server Group By Query using multiple values

Question

I am new to Microsoft SQL Server and have been frustrated by a GROUP BY query that won't do what I want it to. The table is the following:

make    model   distancefrom    distanceto  driverid
toyota  yaris   358.2   368.2   401
toyota  yaris   368.2   378.7   103
toyota  yaris   378.7   382.2   103
toyota  yaris   382.2   392.2   103
toyota  yaris   392.2   403.6   103
toyota  yaris   403.6   414.3   103
toyota  yaris   414.3   419.4   103
toyota  yaris   419.4   430.2   103
toyota  yaris   430.2   439.2   401
toyota  yaris   439.2   446 401
toyota  yaris   446 457.2   401
toyota  yaris   457.2   460.1   401
toyota  yaris   460.1   468.6   401
toyota  yaris   468.6   480.3   401
toyota  yaris   480.3   486.2   103
toyota  yaris   486.2   490 103
toyota  yaris   490 501.1   103
toyota  yaris   501.1   512.5   103
toyota  yaris   512.5   523.1   103
toyota  yaris   523.1   532.6   401
toyota  yaris   532.6   542.7   401
toyota  yaris   542.7   551.1   401

I need to find the start and end value for each of the driverid's. A normal GROUP BY query using a MIN and MAX groups all the driverids that are the same together but I need to keep each separate. I.e. the output should look like this:

make    model   distancefrom    distanceto  driverid
toyota  yaris   358.2   368.2   401
toyota  yaris   368.2   430.2   103
toyota  yaris   430.2   480.3   401
toyota  yaris   480.3   523.1   103
toyota  yaris   523.1   551.1   512

Any help would be appreciated.

Answer 1

Looks to me like you need something like this:

SELECT make, model, driverid, min(distancefrom), max(distancefrom), min(distanceto), max(distanceto)
FROM table
GROUP BY make, model, driverid

Is this the query you run?

Answer 2

SELECT  make, model, driverid
FROM    (
        SELECT  make, model, driverid,
                ROW_NUMBER() OVER (PARTITION BY make, model, driverid ORDER BY distancefrom) AS rns,
                ROW_NUMBER() OVER (PARTITION BY make, model, driverid ORDER BY distancefrom DESC) AS rne
        FROM    mytable
        ) q
WHERE   1 IN (rns, rne)

If you have an index on (make, model, driverid, distance), this one can be more efficient:

SELECT  m.make, m.model, m.driverid,
        (
        SELECT  TOP 1 distancefrom
        FROM     mytable mi
        WHERE    mi.make = m.make
                 AND mi.model = m.model
                 AND mi.driverid = m.driverid
        ORDER BY
                 distancefrom
        ),
        (
        SELECT  TOP 1 distancefrom
        FROM     mytable mi
        WHERE    mi.make = m.make
                 AND mi.model = m.model
                 AND mi.driverid = m.driverid
        ORDER BY
                 distancefrom DESC
        )
FROM    (
        SELECT  DISTINCT make, model, driverid
        FROM    mytable
        ) m

Answer 3

You clearly want to keep journeys separate - the results you say you want for e.g. driver 401 involve the first row, then min/max run over the second set of rows for that driver. So it looks as if you are missing a "JourneyID" column or similar. The first row would have one value for JourneyID, the second set of rows would all have the same value of JourneyID but different to that for the first row.

If you have that column, you can add it to the Group By clause.

Answer 4

MS SQL 2005/2008 solution. Will not work on MS SQL 2000.

create table #journeySegment (make varchar(100) not null
        , model varchar(100) not null
        , distanceFrom decimal(10,2) not null
        , distanceTo decimal(10, 2) not null
        , driverId int not null -- References blah
        , CONSTRAINT data_U unique (make, model, driverId, distanceFrom)
        , CONSTRAINT data_FromTo_CHK check (distanceFrom <= distanceTo))


insert into #journeySegment values ('toyota', 'yaris', 358.2, 368.2, 401)
insert into #journeySegment values ('toyota', 'yaris', 368.2, 378.7, 103)
insert into #journeySegment values ('toyota', 'yaris', 378.7, 382.2, 103)
insert into #journeySegment values ('toyota', 'yaris', 382.2, 392.2, 103)
insert into #journeySegment values ('toyota', 'yaris', 392.2, 403.6, 103)
insert into #journeySegment values ('toyota', 'yaris', 403.6, 414.3, 103)
insert into #journeySegment values ('toyota', 'yaris', 414.3, 419.4, 103)
insert into #journeySegment values ('toyota', 'yaris', 419.4, 430.2, 103)
insert into #journeySegment values ('toyota', 'yaris', 430.2, 439.2, 401)
insert into #journeySegment values ('toyota', 'yaris', 439.2, 446, 401)
insert into #journeySegment values ('toyota', 'yaris', 446, 457.2, 401)
insert into #journeySegment values ('toyota', 'yaris', 457.2, 460.1, 401)
insert into #journeySegment values ('toyota', 'yaris', 460.1, 468.6, 401)
insert into #journeySegment values ('toyota', 'yaris', 468.6, 480.3, 401)
insert into #journeySegment values ('toyota', 'yaris', 480.3, 486.2, 103)
insert into #journeySegment values ('toyota', 'yaris', 486.2, 490, 103)
insert into #journeySegment values ('toyota', 'yaris', 490, 501.1, 103)
insert into #journeySegment values ('toyota', 'yaris', 501.1, 512.5, 103)
insert into #journeySegment values ('toyota', 'yaris', 512.5, 523.1, 103)
insert into #journeySegment values ('toyota', 'yaris', 523.1, 532.6, 513)
insert into #journeySegment values ('toyota', 'yaris', 532.6, 542.7, 513)
insert into #journeySegment values ('toyota', 'yaris', 542.7, 551.1, 513)

-- ASSUMPTIONS:
-- journeySegments do not overlap.
-- distanceFrom and distanceTo are exact numeric types.

; with potentialJourney (make, model, journeyFrom, journeyTo, driverId, level) as
    (-- Find the starting segment for each journey.
    select make,
     model, 
     distanceFrom, 
     distanceTo,
     driverId,
     0
    from #journeySegment A
    where not exists
     (select *
     from #journeySegment B
     where B.make = A.Make
     and B.model = A.model
     and B.driverId = A.driverId 
     and B.DistanceTo = A.DistanceFrom)
    union all
    -- add on next segment.
    select PJ.make
     , PJ.model
     , PJ.journeyFrom
     , nextJS.distanceTo
     , PJ.driverId
     , PJ.level + 1
    from potentialJourney PJ
    inner join #journeySegment nextJS
     on nextJS.make = PJ.Make
     and nextJS.model = PJ.Model
     and nextJs.driverId = PJ.driverId
     and nextJs.distanceFrom = PJ.journeyTo)
select M.make
    , M.Model
    , M.journeyFrom
    , M.journeyTo
    , M.driverId
from potentialJourney M
-- Eliminate the partial solutions
where not exists
    (select *
    from potentialJourney S
    where S.make = M.make
    and S.model = M.model
    and S.journeyFrom = M.journeyFrom
    and S.driverId = M.driverId
    and S.level > M.level)
order by journeyFrom