How make order for avery UNION SELECT?

Question

1.00/5 (1 vote)

See more:

SQL

SELECT "type", idTYPEORDER AS "id", TYPEORDERNAME'.$this->prefix.' AS "name", TYPEORDERNAME as name_d FROM typeorder UNION
SELECT "estate", idTYPE, TYPENAME'.$this->prefix.', TYPENAME FROM type_estate UNION
SELECT "flats", idAPARTAMENT_FLATS, APARTAMENT_FLATSNAME'.$this->prefix.', APARTAMENT_FLATSNAME FROM apartament_flats UNION
SELECT "currency", idCURRENCY, CURRENCYNAME, CURRENCYNAME FROM currency UNION
SELECT "country", idCOUNTRY, COUNTRYNAME'.$this->prefix.', COUNTRYNAME FROM country UNION
SELECT "region", idREGION, REGIONNAME'.$this->prefix.', REGIONNAME FROM region UNION
SELECT "city", idCITY, CITYNAME'.$this->prefix.', CITYNAME FROM city UNION
SELECT "village", idVILLAGE, VILLAGENAME'.$this->prefix.',VILLAGENAME FROM village UNION
SELECT "bedroom", idBEDROOM, BEDROOMNAME'.$this->prefix.', BEDROOMNAME FROM bedroom UNION
SELECT "position", idPOSITION, POSITIONNAME'.$this->prefix.', POSITIONNAME FROM position UNION
SELECT "toilet", idTOILET, TOILETNAME'.$this->prefix.', TOILETNAME FROM toilet UNION
SELECT "view", idVIEW, VIEWNAME'.$this->prefix.', VIEWNAME FROM view UNION
SELECT "flat", idAPARTAMENT_FLAT, APARTAMENT_FLATNAME'.$this->prefix.', APARTAMENT_FLATNAME FROM apartament_flat UNION
SELECT "repair", idREPAIRTYPE, REPAIRTYPENAME'.$this->prefix.', REPAIRTYPENAME FROM repairtype UNION
SELECT "project", idPROJECT, PROJECTNAME'.$this->prefix.', PROJECTNAME FROM project UNION
SELECT "rooms", idApartamentrooms, APARTAMENTROOMS'.$this->prefix.', APARTAMENTROOMS FROM apartament_rooms UNION
SELECT "position_type", idPOSITION, POSITIONNAME'.$this->prefix.', POSITIONNAME FROM position_type UNION
SELECT "comfort", idCOMFORT, COMFORTNAME'.$this->prefix.', COMFORTNAME FROM comfort UNION
SELECT "metro", idMETRO, METRONAME'.$this->prefix.', METRONAME FROM metro

I want that every SELECT will be with ORDER BY 'name_field' DESC
Is it possible?

Posted 17-Jun-13 8:38am

Jardin1

Add a Solution

Comments

Kschuler 17-Jun-13 15:25pm

Not sure if it's the same syntax as the SQL I use...but when I have unions and need to order by, I just put the ORDER BY on the last select statement and it works. (Although I usually alias all of the fields I pull...but not sure if that's required.)

CHill60 17-Jun-13 17:22pm

Post this as the solution ... you're not actually putting order by on the "last select statement", you're putting the order by on the entire result set (of all of the unions). I'm slightly disappointed in the OP's question though ... "suck it and see" would have been the order of the day :-)

Kschuler 17-Jun-13 17:28pm

or google.

mgoad99 17-Jun-13 15:26pm

First, I don't see a 'name_field' in your query. If that is the 1st field, then on the first select statement do this: SELECT "type" as name_field

Then after the last select statement, add ORDER BY 'name_field' DESC. This will order the entire set (the combined data from all of the select statements) by the name_field

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kschuler · Answer 1 · 2013-06-17T11:28:00

Add the ORDER BY once and to the end of the statement you have so far.

Next time you should just try it and see what works. Also, there were a lot of possible answers you could have found on google.[^]. The first result[^], a little ways down, has an answer that says you can just use ORDER BY 2 if you want to order by the second column that you are returning. SO there is another option for you. Just do order by whichever number column you want.

Good luck.