Forum > Databases

Firebird: Order by on join.

(1/3) > >>

iru:
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.

Select PROGRAM_ID, PROGRAM_EVENT From PROGRAMDATA
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.

Zvoni:
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

iru:
Zvoni,

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.

Ian

devEric69:
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;

Zvoni:
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
https://www.wisdomjobs.com/e-university/firebird-tutorial-210/general-form-of-order-by-clause-7812.html

--- 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.

Navigation

[0] Message Index

[#] Next page

Go to full version