Query to serialize data

Question

I have two tables:

Routes

ID   Description
1    street1
2    street2
3    street3
4    street4
5    street5

Segments 

ID  RouteID, Progres, LabelStart, LabelEnd
1   1        5            1 A        21 B
2   1        10           2 A        10 
3   2        15           3          25 
4   2        15           2          20
5   3        20           1          11 
6   3        22           4          10
7   4        30           5          11 
8   4        31           2          12

I need a sequence with these rules:

1. table must be ordered by Progress ASC
2. A column Type is defined and take O if LabelStart and LabelEnd are Odd, E if Even
3. if two routes have the same progress then the rows are merged in one where LabelStart is the minimum (among LabelStart Odd and LabelStart Even) and LabelEnd is the Max, in this case Type takes the value of A (All)

as per example data above the result should be

Sequence  
ID  RouteID, Progres, LabelStart, LabelEnd  Type
1   1        5            1 A        21 B    O
2   1        10           2 A        10      E 
4   2        15           2          25      A
5   3        20           1          11      O
6   3        22           4          10      E
7   4        30           5          11      O
8   4        31           2          12      E

It is for Postgres 9.2

Answer 1

This was an interesting query to write because you had letters in your LabelStart and LabelEnd fields. I used REGEX_REPLACE to remove those. Then I used a CTE to get the records that had more than one routeid and progress rows.

I think this should do it:

WITH CTE AS (
  SELECT 
    RouteId, Progress
  FROM Sequence
  GROUP BY RouteId, Progress
  HAVING COUNT(DISTINCT Id) > 1
  )
SELECT MAX(S.ID) Id,
  T.RouteId,
  T.Progress,
  MIN(regexp_replace(LabelStart, '[^0-9]', '', 'g')) LabelStart,
  MAX(regexp_replace(LabelStart, '[^0-9]', '', 'g')) LabelEnd,
  'A' as Type
FROM Sequence S 
  INNER JOIN CTE T ON S.RouteId = T.RouteId AND S.Progress = T.Progress
GROUP BY T.RouteId, T.Progress
UNION 
SELECT S.Id,
  S.RouteId,
  S.Progress,
  S.LabelStart,
  S.LabelEnd,
  CASE 
    WHEN CAST(regexp_replace(LabelStart, '[^0-9]', '', 'g') as int) % 2 = 0 
    THEN 'E' 
    ELSE 'O' 
  END  
FROM Sequence S 
  LEFT JOIN  CTE T ON S.RouteId = T.RouteId AND S.Progress = T.Progress
WHERE T.RouteId IS NULL
ORDER BY Progress ASC

And some sample Fiddle.