Firebird: Order by on join.
lazarus / 16-Databases

----------------------------------------------------------------------
iru · 2021-10-27 08:21

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 · 2021-10-27 09:13

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 · 2021-10-27 09:33

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 · 2021-10-27 09:52

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

Code: MySQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT
PROGRAM_ID, PROGRAM_EVENT, EVENT_NO FROM PROGRAMDATA    INNER JOIN
EVENTDATA ON PROGRAMDATA.Program_ID = EVENTDATA.Event_NOORDER BY
EVENT_NO ASC;

----------------------------------------------------------------------
Zvoni · 2021-10-27 10:00

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

----------------------------------------------------------------------
devEric69 · 2021-10-27 10:22

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

Correct. You're right.

----------------------------------------------------------------------
devEric69 · 2021-10-27 11:32

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  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 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 · 2021-10-27 13:02

Take in the test the sorted field to see if it really is sorted.
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 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 · 2021-10-27 22:21

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 · 2021-10-28 06:26

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

----------------------------------------------------------------------
rvk · 2021-10-28 08:45

There is nothing wrong (as far as I can see) with the SQL from the
original post. And it should sort the way you described.

But I'm starting to believe there is something else wrong. How are you
programming the SQL in the TIBQuery? In code or in the Object
Inspector? Or both? Maybe you are filling the SQL in the wrong place
and there is some override going on. (especially because you say it
does work correctly in a SQL editor)

devEric69 already suggested it but it might be best you create a small
project where you test your SQL yourself and if it's still giving
problems you can post it here (via publish button in the IDE).

----------------------------------------------------------------------
iru · 2021-10-28 23:59

Gentlefolk,
Thanks for the responses. I have to delay further work on this for a
couple of days as some ugly weather with strong winds has caused
damage to athletics facilities that I look after. off to work I go....
Ian

----------------------------------------------------------------------
iru · 2021-11-02 02:36

Gentlefolk,

I have been rather busy, strong winds around Melbourne last Thursday
have caused extensive damage in many areas. There are still thousands
of homes without electric power. 

The SQL is loaded in code.

There are two IBQuery:   IBQProgramData and  IBQEventData.
The SQL is written to IBQProgramData as follows:

        BaseDM.DMB.IBQProgramData.SQL.Clear;
        BaseDM.DMB.IBQProgramData.SQL.AddDelimitedText(cEventJoinSQL,
'%', True);

Where cEventJoinSQL is a constant
cEventJoinSQL    =  'Select PROGRAM_ID, PROGRAM_EVENT From PROGRAMDATA
%' +
                      'inner join EVENTDATA on PROGRAMDATA.Program_ID
= EventData.Event_NO %' +
                      'order by EventData.EVENT_NO asc'; 

If I change 'order' to 'xxxorder' there is an ugly SQL error when the
transaction on IBQProgramData is started.

Ian

----------------------------------------------------------------------
Zvoni · 2021-11-02 06:47

AddDelimitedText?????
Why the blazes are you using that?
Put the SQL in one line (no LineBreaks) and just use
"..blablabla.SQL.Text:=" and be done with it
And remove the '%' from the SQL-String