按Leg分组，但也加入属于最早收集时间的网站

Question

I need to do the following. I have 9 records linked by a schedule_id. these 9 records are broken into 3 sets of 3 for example of Leg 1 leg 2 and leg 3, so i have the following

order sched leg site   date
1      1     1   1     2014-01-01
2      1     2   34    2014-12-31
3      1     1   15    2014-04-23
4      1     3   4     2014-07-12
5      1     2   89    2014-04-10
6      1     1   13    2014-09-09
7      1     3   9     2014-03-12
8      1     3   10    2014-11-10
9      1     2   11    2014-08-08

In my sql I group by leg so I land up with 3 records. I need to extract the site ids that belong to the earliest and latest date in each leg group and then join my site information table to the resulting ids to extractcompanynames etc for these sites.

my sql code for the legs is as follows

select l.leg_no, 
                  l.start_region, 
                  r.region_name as end_region, 
                  l.rate,
                  sum(x.est_distance) as distance,
                  sum(x.est_weight) as weight,
                  count(x.order_id) as count, 
                  min(x.req_col_time) as earliesttime,
                  sum(x.total_rpb) as total_rpb, 
                  max(x.req_del_time) as latesttime from 
                  (select ord2.* from loads as l1
                  left join orders as ord2 on ord2.load_id = l1.load_id
                  left join debrief_docs as db2 on db2.oid = ord2.order_id
                  where l1.schedule_id = '.$id.'
                  union
                  select ord3.* from loads as l2
                  left join drops as drop1 on drop1.load_id = l2.load_id
                  left join orders as ord3 on ord3.drop_id = drop1.drop_id
                  left join debrief_docs as db3 on db3.oid = ord3.order_id
                  where l2.schedule_id = '.$id.')as x
                  join loads as l on l.schedule_id = '.$id.'
                  join regions as r on r.region_id = l.region_id
                  group by l.leg_no asc

Any ideas on how to achieve this would be greatly appreciated. ;) thank you all

Answer 1

Let's approach the important part of the question, which is getting the first and last site ids for each leg group. I would use variables for this purpose. The following query enumerates the leg ids:

  select l.*,
         (@rn := if(@l = leg_id, @rn + 1,
                    if(@l := leg_id, 1, 1)
                   )
         ) as seqnum
  from loads l cross join
       (select @rn := 0, @l := 0) vars
  order by schedule_id, leg_id, date;

The following summarizes the records and produces two columns, the first and last site:

select schedule_id, leg_id,
       max(case when seqnum = 1 then site_id end) as first_site,
       max(case when seqnum = 3 then site_id end) as last_site
from (select l.*,
             (@rn := if(@l = leg_id, @rn + 1,
                        if(@l := leg_id, 1, 1)
                       )
             ) as seqnum
      from loads l cross join
           (select @rn := 0, @l := 0) vars
      order by schedule_id, leg_id, date
     ) l
group by schedule_id, leg_id;

You can then join in the rest of the tables to get the additional information that you want.