[SQL] Sybase - How to use "ORDER BY" in derived table

After a while I'm again asking for help, as I couldn't think of solution.

I have this SQL query:

SELECT * FROM (SELECT TOP 1
ac_registr,
event,
CASE
WHEN dimension = "C" THEN "Cycles"
END AS "dimension",
togo AS "togo cycles",
CEILING (0) AS "togo days",
FLOOR (0) AS "togo hours",
absolute_due_at_ac AS "Due at cycles",
CONVERT( char(10), 0) AS "Due at date",
FLOOR (0) AS "Due at hours",
CONVERT( char(10), dateadd(day, expected_date, 'DEC 31 1971'), 104) AS "expected_date_of_perform",
event_display
FROM forecast
WHERE ac_registr IN ('HEU')
AND dimension = 'C'
AND expected_date <= 19669
AND expected_date <> 0
/*ORDER BY togo ASC*/) C

UNION

SELECT * FROM (SELECT TOP 1
ac_registr,
event,
CASE
WHEN dimension = "D" THEN "Days"
END AS "dimension",
0 AS "togo cycles",
CEILING (togo/1439) AS "togo days",
FLOOR (0) AS "togo hours",
0 AS "Due at cycles",
CONVERT( char(10), dateadd(day, absolute_due_at_ac, 'DEC 31 1971'), 104) AS "Due at date",
FLOOR (0) AS "Due at hours",
CONVERT( char(10), dateadd(day, expected_date, 'DEC 31 1971'), 104) AS "expected_date_of_perform",
event_display
FROM forecast
WHERE ac_registr IN ('HEU')
AND dimension = 'D'
AND expected_date <= 19669
AND expected_date <> 0
/*ORDER BY togo ASC*/) D

UNION

SELECT * FROM(SELECT TOP 1
ac_registr,
event,
CASE
WHEN dimension = "H" THEN "Hours"
END AS "dimension",
0 AS "togo cycles",
CEILING (0) AS "togo days",
FLOOR (togo/60) AS "togo hours",
0 AS "Due at cycles",
CONVERT( char(10), 0) AS "Due at date",
FLOOR (absolute_due_at_ac/60) AS "Due at hours",
CONVERT( char(10), dateadd(day, expected_date, 'DEC 31 1971'), 104) AS "expected_date_of_perform",
event_display
FROM forecast
WHERE ac_registr IN ('HEU')
AND dimension = 'H'
AND expected_date <= 19669
AND expected_date <> 0
/*ORDER BY togo ASC*/) H


The query contain three "blocks" of sub-queries, each returning only one row of values, which are needed. All the data comes from one table, but some of them (the important ones) needs to be calculated or converted. That is not a problem as shown data are in right format.

The trouble is, I did each block separately, so I could see, if the result is the one we needed. Then I connected the results together via UNION command, and if I put all ORDER BY clauses into commentary, it shows results in appropriate format, but with incorrect data (because they are no longer ordered).

I also tried to think of different solution, where I work with ordered data, but I don't know, how to select only "top row" of each dimension. The query for that is:

SELECT ac_registr,
event,
event_type,
CASE
WHEN dimension = "C" THEN "Cycles"
WHEN dimension = "D" THEN "Days"
WHEN dimension = "H" THEN "Hours"
END AS "dimension",
togo,
absolute_due_at_ac,
CONVERT( char(10), dateadd(day, expected_date, 'DEC 31 1971'), 104) AS "expected_date"
FROM forecast
WHERE ac_registr IN ('HEU')
AND dimension IN ('C', 'D', 'H')
AND expected_date <= 19669
AND expected_date <> 0
ORDER BY dimension, togo ASC


but there I have no idea, how to select only relative rows (top one for each dimension). I couldn't use fixed row number, as that may vary, and when I tried to use CASE command, it returned errors.

Is it possible to somehow order the data in the derived tables in first case? Or how to select the first row for each dimension in second case ?

I genuinely have no idea, so I would appreciate any help.

Also, I'll add a note, it is not possible to only order the data by forecast.togo column, since all the requirements are in different value size, as you may noticed in the first query.

Thank you in advance, Sincerely, Karel

Continue reading...