sql order by - SQL Firebird Query giving incorrect output -

i have following firebird query:

select distinct"sorter", "week" "week", "year", "course" "course" ( select (extract(yearday datesent) - extract(weekday datesent-1)+7)/7 "sorter", case extract(week datesent) when 1 'week # 1 (2 jan) ' when 2 'week # 2 (09 jan) ' when 3 'week # 3 (16 jan) ' when 4 'week # 4 (23 jan)' when 5 'week # 5 (30 jan)' when 6 'week # 6 (06 feb)' when 7 'week # 7 (13 feb)' when 8 'week # 8 (20 feb)' when 9 'week # 9 (27 feb)' when 10 'week # 10 (06 march)' when 11 'week # 11 (13 march)' when 12 'week # 12 (20 march)' when 13 'week # 13 (27 march)' when 14 'week # 14 (03 april)' when 15 'week # 15 (10 april)' when 16 'week # 16 (17 april)' when 17 'week # 17 (24 april)' when 18 'week # 18 (01 may)' when 19 'week # 19 (08 may)' when 20 'week # 20 (15 may)' when 21 'week # 21 (22 may)' when 22 'week # 22 (29 may)' when 23 'week # 23 (05 jun)' when 24 'week # 24 (12 jun)' when 25 'week # 25 (19 jun)' when 26 'week # 26 (26 jun)' when 27 'week # 27 (03 july)' when 28 'week # 28 (10 july)' when 29 'week # 29 (17 july)' when 30 'week # 30 (24 july)' when 31 'week # 31 (31 july)' when 32 'week # 32 (07 aug)' when 33 'week # 33 (14 aug)' when 34 'week # 34 (21 aug)' when 35 'week # 35 (28 aug)' when 36 'week # 36 (04 sep)' when 37 'week # 37 (11 sep)' when 38 'week # 38 (18 sep)' when 39 'week # 39 (25 sep)' when 40 'week # 40 (02 oct)' when 41 'week # 41 (09 oct)' when 42 'week # 42 (16 oct)' when 43 'week # 43 (23 oct)' when 44 'week # 44 (30 oct)' when 45 'week # 45 (06 nov)' when 46 'week # 46 (13 nov)' when 47 'week # 47 (20 nov)' when 48 'week # 48 (27 nov)' when 49 'week # 49 (04 dec)' when 50 'week # 50 (11 dec)' when 51 'week # 51 (18 dec)' when 52 'week # 52 (25 dec)' end  "week" , extract(year datesent) "year" , course "course"   programmers   --** change between date select courses starting between 2 dates format yyyy-mm-dd **-- datesent between '2012-12-20' , '2013-12-31'  group 1,2,3,4  union  select (extract(yearday booking_date) - extract(weekday  booking_date-1)+7)/7 "sorter", case extract(week booking_date) when 1 'week # 1 (2 jan) ' when 2 'week # 2 (09 jan) ' when 3 'week # 3 (16 jan) ' when 4 'week # 4 (23 jan)' when 5 'week # 5 (30 jan)' when 6 'week # 6 (06 feb)' when 7 'week # 7 (13 feb)' when 8 'week # 8 (20 feb)' when 9 'week # 9 (27 feb)' when 10 'week # 10 (06 march)' when 11 'week # 11 (13 march)' when 12 'week # 12 (20 march)' when 13 'week # 13 (27 march)' when 14 'week # 14 (03 april)' when 15 'week # 15 (10 april)' when 16 'week # 16 (17 april)' when 17 'week # 17 (24 april)' when 18 'week # 18 (01 may)' when 19 'week # 19 (08 may)' when 20 'week # 20 (15 may)' when 21 'week # 21 (22 may)' when 22 'week # 22 (29 may)' when 23 'week # 23 (05 jun)' when 24 'week # 24 (12 jun)' when 25 'week # 25 (19 jun)' when 26 'week # 26 (26 jun)' when 27 'week # 27 (03 july)' when 28 'week # 28 (10 july)' when 29 'week # 29 (17 july)' when 30 'week # 30 (24 july)' when 31 'week # 31 (31 july)' when 32 'week # 32 (07 aug)' when 33 'week # 33 (14 aug)' when 34 'week # 34 (21 aug)' when 35 'week # 35 (28 aug)' when 36 'week # 36 (04 sep)' when 37 'week # 37 (11 sep)' when 38 'week # 38 (18 sep)' when 39 'week # 39 (25 sep)' when 40 'week # 40 (02 oct)' when 41 'week # 41 (09 oct)' when 42 'week # 42 (16 oct)' when 43 'week # 43 (23 oct)' when 44 'week # 44 (30 oct)' when 45 'week # 45 (06 nov)' when 46 'week # 46 (13 nov)' when 47 'week # 47 (20 nov)' when 48 'week # 48 (27 nov)' when 49 'week # 49 (04 dec)' when 50 'week # 50 (11 dec)' when 51 'week # 51 (18 dec)' when 52 'week # 52 (25 dec)' end "week", extract(year booking_date) "year" , '' "course"  comp_invoice booking_date between '2012-12-20' , '2013-12-31'   ) group 1,2,3,4 order 1,3 asc 

it returns following:

 sorter - week - year - course 0      -week1 - 2013 -   1      -week2 - 2013 -  1      -week2 - 2013 - whs  2      -week3 - 2013 -   3      -week4 - 2013 -   3      -week4 - 2013 - whs  4      -week5 - 2013 -   4      -week5 - 2013 -  5      -week6 - 2013 -   5      -week6 - 2013 - ref 

as can seen 2 rows week2, week4, week5 , week6 need return follows:

  sorter - week - year - course 0      -week1 - 2013 -  1      -week2 - 2013 - whs 2      -week3 - 2013 -  3      -week4 - 2013 - whs 4      -week5 - 2013 - 5      -week6 - 2013 - ref 

any suggestions?

derek, looks want max value why not try this;

select "sorter", "week", "year", max("course") (      "your query goes here goes inside 2 parenthesis" ) t1 group 1,2,3 

or if can have multiple courses on same week use list instead of max

hope helps.