sql用的求每组中最大和联合

select  e.txt_desig,
          e.routelevel,
          d.c,
         d.en_route_rte_id,
         t.code_startpointid,
         t.code_startfir,
         t.code_type_start,
         t.code_endpointid,
         t.code_endfir,
         t.code_type_endpoint,
         d.code_dir
    from en_route_rte e,segment t,
   
         (select g.en_route_rte_id, m.c, code_dir,g.segment_id
            from rte_seg g,
                 (select EN_ROUTE_RTE_ID, max(code_sort) as c
                    from rte_seg
                   group by EN_ROUTE_RTE_ID
                   order by EN_ROUTE_RTE_ID) m
           where m.en_route_rte_id = g.en_route_rte_id
             and m.c = g.code_sort
                  union select en_route_rte_id,code_sort,code_dir,segment_id from rte_seg where code_sort=1) d
    where e.en_route_rte_id = d.en_route_rte_id
    and t.segment_id=d.segment_id
order by d.en_route_rte_id,d.c