Print Page - Firebird: Order by on join.

Programming => Databases => Topic started by: iru on October 27, 2021, 10:21:41 am

Title: Firebird: Order by on join.
Post by: iru on October 27, 2021, 10:21:41 am

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.

Title: Re: Firebird: Order by on join.
Post by: Zvoni on October 27, 2021, 11:13:13 am

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

Title: Re: Firebird: Order by on join.
Post by: iru on October 27, 2021, 11:33:18 am

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

Title: Re: Firebird: Order by on join.
Post by: devEric69 on October 27, 2021, 11:52:50 am

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

Code: MySQL [Select][+]

SELECT PROGRAM_ID, PROGRAM_EVENT, EVENT_NO 
FROM PROGRAMDATA
    INNER JOIN EVENTDATA ON PROGRAMDATA.Program_ID = EVENTDATA.Event_NO
ORDER BY EVENT_NO ASC; 

Title: Re: Firebird: Order by on join.
Post by: Zvoni on October 27, 2021, 12:00:40 pm

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.

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.

Title: Re: Firebird: Order by on join.
Post by: devEric69 on October 27, 2021, 12:22:23 pm

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.

Correct. You're right.

Title: Re: Firebird: Order by on join.
Post by: devEric69 on October 27, 2021, 01:32:50 pm

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][+]

Select
-- table A
A.FIELD_ID, ...\...,
-- table B
B.FIELD_ID, ...\...,
-- table C
C.FIELD_ID, ...\...,
-- table D
D.FIELD_ID, ...\...,
-- table E
E.FIELD_ID, ...\...,
-- table F
F.FIELD_ID, ...\...
From
TABLE_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 BY
A.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.

Title: Re: Firebird: Order by on join.
Post by: mangakissa on October 27, 2021, 03:02:43 pm

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

Code: Pascal [Select][+]

Select EventData.EVENT_NO, EVENTDATA.PROGRAM_ID, EVENTDATA.PROGRAM_EVENT From PROGRAMDATA
inner join EVENTDATA on
PROGRAMDATA.Program_ID = EventData.Event_NO
order by EventData.EVENT_NO;  
 

Title: Re: Firebird: Order by on join.
Post by: iru on October 28, 2021, 12:21:02 am

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.

Title: Re: Firebird: Order by on join.
Post by: Zvoni on October 28, 2021, 08:26:15 am

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

Title: Re: Firebird: Order by on join.
Post by: rvk on October 28, 2021, 10:45:11 am

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

Title: Re: Firebird: Order by on join.
Post by: iru on October 29, 2021, 01:59:14 am

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

Title: Re: Firebird: Order by on join.
Post by: iru on November 02, 2021, 03:36:22 am

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

Title: Re: Firebird: Order by on join.
Post by: Zvoni on November 02, 2021, 07:47:57 am

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

Lazarus

Programming => Databases => Topic started by: iru on October 27, 2021, 10:21:41 am