Forum > Databases

Firebird: Order by on join.

<< < (2/3) > >>

devEric69:

--- Quote from: Zvoni on October 27, 2021, 12:00:40 pm ---And it wouldn't explain his second try according to my suggestion to sort by PROGRAM_ID since this IS a output-column.

--- End quote ---

Correct. You're right.

devEric69:
Sorry, but TIBQuery works fine for me (with its Database and Transaction properties set; nothing more changed, in settings) with a query like this:


--- 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-- table AA.FIELD_ID, ...\...,-- table BB.FIELD_ID, ...\...,-- table CC.FIELD_ID, ...\...,-- table DD.FIELD_ID, ...\...,-- table EE.FIELD_ID, ...\...,-- table FF.FIELD_ID, ...\...FromTABLE_A AS A  LEFT OUTER JOIN TABLE_B AS B ON (A.FIELD_ID = B.FIELD_ID)    LEFT OUTER JOIN TABLE_C AS C ON (B.FIELD_ID = C.FIELD_ID)    LEFT OUTER JOIN TABLE_D AS D ON (B.FIELD_ID = D.FIELD_ID)    LEFT OUTER JOIN TABLE_E AS E ON (B.FIELD_ID = E.FIELD_ID)    LEFT OUTER JOIN TABLE_F AS F ON (B.FIELD_ID = F.FIELD_ID)ORDER BYA.FIELD_ID DESC; -- or ASC
I've replaced a TIBDataset by a TIBQuery, TIBQuery pointed by a datasource, datasource pointed by a TIBdynamicGrid: when I change the ORDER BY ASC to ORDER BY DESC in the SQL clause, the display order of the records visually changes in the dbGrid.

nb: I've a primary \ unique index on my primary keys, and simple b-tree secondary indexes on my foreign keys.
suggestion: publish a small project with the request inside.

               

mangakissa:
Take in the test the sorted field to see if it really is sorted.

--- Code: Pascal  [+][-]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 EventData.EVENT_NO, EVENTDATA.PROGRAM_ID, EVENTDATA.PROGRAM_EVENT From PROGRAMDATAinner join EVENTDATA onPROGRAMDATA.Program_ID = EventData.Event_NOorder by EventData.EVENT_NO;   

iru:
Gentlefolk,

Thank you for your responses. I have done some more work on this issue:

The SQL provided by mangakissa fails with complaints about unknown fields.

From the example provided by devEric69 I developed the following SQL which executes with no errors but does NOT sort the EVENTDATA table.

Select A.PROGRAM_ID, A.PROGRAM_EVENT from PROGRAMDATA as A
  LEFT OUTER JOIN EVENTDATA as B on (A.PROGRAM_ID = B.EVENT_NO)
order by A.Program_Event asc;

So as the results of the SQL select are transferred to a StringGrid I used

  OutGrid.SortOrder := soAscending;
  OutGrid.SortColRow(True, 2);

and things are sorted and displayed correctly....

I would however like to know how to use the SQl.

Thanks, ian.




Zvoni:
Sorry to say, but that SQL makes no sense.
You're exclusively selecting columns from PROGRAMDATA, LEFT JOIN-ing it with EVENTDATA, and sorting by a column from PROGRAMDATA.
You do realize that with a LEFT JOIN you get ALL records from the left table?

Leave out the LEFT JOIN....

SELECT PROGRAM_ID, PROGRAM_EVENT FROM PROGRAMDATA ORDER BY PROGRAM_EVENT

Navigation

[0] Message Index

[#] Next page

[*] Previous page

Go to full version