Trouble adding ORDER BY to SQL statement

[DeathRobot]

Hi. I'm having trouble adding ORDER BY to an already working SQL statement. This statement works (the global variables are grabbed from two other looping scripts):

 

SELECT

People.id

 

FROM

People

 

LEFT JOIN

Companies ON People.id_Company = Companies.id

 

WHERE " & 

$$peopleWHERE 

 

&

 

" UNION

 

SELECT

id_People

 

FROM

DevPlayed

 

LEFT JOIN

DevInstruments ON DevPlayed.id_instruments = DevInstruments.id

 

WHERE " &

$$instrumentWHERE

 

;

 

 

 

Adding the ORDER BY causes it to fail:

 

 

 

SELECT

People.id

 

FROM

People

 

LEFT JOIN

Companies ON People.id_Company = Companies.id

 

WHERE " & 

$$peopleWHERE 

 

&

 

" UNION

 

SELECT

id_People

 

FROM

DevPlayed

 

LEFT JOIN

DevInstruments ON DevPlayed.id_instruments = DevInstruments.id

 

WHERE " &

$$instrumentWHERE

 

&

 

" ORDER BY People.nameLastFirstMiddleInitial ASC (I've also tried LOWER(People.nameLastFirstMiddleInitial ) ).

"

 

;

 

 

Seems like this should work. Obviously, I'm overlooking something. Any idea what that is?

 

Thanks,

Michael

[Davesmind]

I'm not the goto SQL guru, but your Select statement pulls one specific field (People.id and id_people), Yet your Order By statement is referring to People.name. I don't know what your data set looks like when you run it without the Order By. Are you actually getting that field (people.name) to refer to?

[DeathRobot]

Thanks for the reply. The SQL statement is to populate a portal. I use the "id" field because I know it's never empty, but I could use any non-empty field, I think. I do this same SQL select and ORDER BY on 6 other portals, but those are simpler select statements without UNION or LEFT JOIN. Those all work perfectly, so it really seems to be something to do with the addition of UNION and/or LEFT JOIN. Just to be sure, I did try your suggestion, but I still get an error. I've also tried adding parenthesis:

 

SELECT

People.id

 

FROM

( SELECT

People.id

 

FROM

People

 

etc…

)

&

 

" ORDER BY People.nameLastFirstMiddleInitial ASC (I've also tried LOWER(People.nameLastFirstMiddleInitial ) ).

"

 

and adding an "a" (for alias):

 

SELECT

People.id

 

FROM

( SELECT

People.id

 

FROM

People

 

etc…

)

&

 

" a

ORDER BY People.nameLastFirstMiddleInitial ASC (I've also tried LOWER(People.nameLastFirstMiddleInitial ) ).

"

 

Those don't work either.

[Fitch]

Two things:

 

1. I don't think you can ORDER BY a field that's not included in the SELECT. I could be wrong.

 

2. Are you saying that the returned list of IDs is put into a global field, and the related records are displayed in a portal? If so, did you know that the order of those IDs in the global field will have no effect on the order the records display in the portal?*

 

(*Unless you're using some kind of special portal-sorting technique.)

[DeathRobot]

Thanks, Fitch.

 

First, #2: Yes, that's correct. I am using Mr. Vodka's technique. The SQL gathers the id's and sets a variable to that list, and the portal is set to sort on that global variable.

 

#1: On all my other portals, I SELECT the id but ORDER BY another field. All of them work, and at the times they might not have (while experimenting with the calculations, for example), I never received an error; the records just failed to sort. On the above SQL statement, I always receive a "no records match your request" error, so the whole SQL statement seems to be problematic when including the ORDER BY. If I take it out, the search works; I just don't get any ordering.

 

Michael

[DeathRobot]

And I see that Mr. Vodka credits you for coming up with this!

[beverly]

ORDER BY must be the very last clause regardless of the selects, joins or unions.

I agree that ORDER BY must contain fields by which to sort within the SELECT clause. Try to sort the portal!