Sorting rows?

hsm

I have an ADO query feeding a simple banded report (not a cross tab). I need to sort the rows by one the one of the columns in the query but that column is a calculated one with an alias. Apparently you can't use an alias in an SQL ORDER BY clause and using the column number instead is ignored (and apparently no longer supported in SQL).

Is there a way to sort the data in the report instead? (I could even do the calculation in the report if necessary as its only a subtraction.

A slightly cut down version of my query is below.

Howard

SELECT
    tbl_attendance.Adno,
    tbl_attendance.Student_Surname,

    (SELECT cumulativeAttendance
       FROM    tbl_attendance AS T
       WHERE   t.weeknumber = 14
       AND     t.Adno = tbl_attendance.adno
       ) AS WeekMinus1,

    (SELECT cumulativeAttendance
       FROM    tbl_attendance AS T
       WHERE   t.weeknumber = 15
       AND     t.Adno = tbl_attendance.adno
       ) AS WeekMinus0,

    (WeekMinus0 - WeekMinus1) as Movement

FROM
    tbl_attendance
WHERE
    tbl_attendance.TheYear = 2010
    
ORDER BY
    Movement              <----- this gives an error
                               
                          --- but ---
ORDER BY 
    5                     <-----this is ignored

gpi

FR can't to sort datasets
Try to use

(SELECT cumulativeAttendance
FROM tbl_attendance AS T
WHERE t.weeknumber = 14
AND t.Adno = tbl_attendance.adno
) 
-
(SELECT cumulativeAttendance
FROM tbl_attendance AS T
WHERE t.weeknumber = 15
AND t.Adno = tbl_attendance.adno
) AS  Movement

instead of

(WeekMinus0 - WeekMinus1) as Movement

hsm

gpi wrote: »

FR can't to sort datasets
Try to use

(SELECT cumulativeAttendance
FROM tbl_attendance AS T
WHERE t.weeknumber = 14
AND t.Adno = tbl_attendance.adno
) 
-
(SELECT cumulativeAttendance
FROM tbl_attendance AS T
WHERE t.weeknumber = 15
AND t.Adno = tbl_attendance.adno
) AS  Movement

instead of

(WeekMinus0 - WeekMinus1) as Movement

I did do that but it doesn't help as I still have to write 'ORDER BY movement' and at that point I am trying to sort by an alias - which is not supported.
(I find it rather amazing that FR does not support sorting, especially as it does in crosstabs)
Is there a way I can put another query component in the data page of the report to run a sorting query on the result of my first query?

gpi

Try to use Select from Select statement
This SQL works OK in MS Access

Select * from 
(SELECT tab1.id*3, tab1.id , tab1.name
FROM tab1)
order by 3

wrote:

(I find it rather amazing that FR does not support sorting, especially as it does in crosstabs)

But I do not. FR is a report generator, not database server

hsm

gpi wrote: »

Try to use Select from Select statement
This SQL works OK in MS Access

Select * from 
(SELECT tab1.id*3, tab1.id , tab1.name
FROM tab1)
order by 3

gpi wrote: »

(I find it rather amazing that FR does not support sorting, especially as it does in crosstabs)

But I do not. FR is a report generator, not database server

Good idea, thank you! In effect I ended up doing that by creating a temp table first but a nested select is much more elegant and saves code.

With respect to sorting though, I agree that a report generator is to do with the presentation of data, not the extraction of data.

As such it should operate at the presentation layer, not the data layer of the ISO model.
It should therefore be responsible for layout, positiononing, format and yes, the ordering of the data it displays.
Really we should not make any assumptions about the row order of data obtained via SQL as in essence SQL only performs mathematics on sets.
(ie you cannot assume that the rows of a table you look at really are in that order and whilst using ORDER By <columnnumber> is still syntactically correct it will be ignored by the SQL 99 standard and later)

Still, thanks again for the suggestion.
Howard