Forum > Databases

Firebird: Order by on join.

(1/3) > >>

Environment: Linux Mint 20.2, Laz 2.0.12, FPC 3.2.2, FB 3.0

Gentlefolk, I have the following SQL on an IBQuery. Works well, reads table and all records are accessible in the IBQuery for table EventData.

inner join EVENTDATA on
PROGRAMDATA.Program_ID = EventData.Event_NO

I wish to sort the records on EventData.Event_NO.
I have tried appending various versions of

   order by EventData.EVENT_NO asc; 

With NO success. Test ok in the SQL editor but records do not get sorted.

Any ides, suggestions appreciated, Ian.

Have you tried sorting by PROGRAMDATA.Program_ID? Should give the same (expected) order since you use an INNER JOIN

EDIT: I consider it bad practice to Sort by a not selected column, since at first look the order could appear to be arbitrary.
Even if in your case the sort-order should be the same, since you're joining on program_id=event_no


Thanks for the rapid response.

I tried 'order by ProgramData.Program_ID desc;' (and asc).

Checks OK in the SQL editor but no success, records are still out of order.


You must select the field to be sorted in the Select clause too, even if you don't display it:

--- Code: MySQL  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---SELECT PROGRAM_ID, PROGRAM_EVENT, EVENT_NO FROM PROGRAMDATA    INNER JOIN EVENTDATA ON PROGRAMDATA.Program_ID = EVENTDATA.Event_NOORDER BY EVENT_NO ASC;

Eric, since when?
I was under the impression as long as there is no UNION or GROUP BY-Clause you can sort by a not selected column

--- Quote ---In UNION and GROUP BY queries, the column used for sorting has to be present in the output list. In other queries, non-output columns and (in v.1.5) expressions on non-output columns are valid ordering criteria.

--- End quote ---
And it wouldn't explain his second try according to my suggestion to sort by PROGRAM_ID since this IS a output-column

EDIT: My money is on this IBQuery-Thing (never used it).
I've found some references to a Property "IndexFieldNames" (or some such) which would allow sorting on the Query after it has been called.


[0] Message Index

[#] Next page

Go to full version