Jump to content

Trouble adding ORDER BY to SQL statement


This topic is 2825 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • 3 weeks later...

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!

Link to comment
Share on other sites

This topic is 2825 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.