whats the difference??

[DanDaBeginner]

im trying to select a country but with USA and CANADA first and others to be arranged alphabetically, so I have this query.

 

but the problem is when used this query with both select statement enclosed in brackets theres no row selected

 

(SELECT `country_name` FROM `country` as t2
WHERE `country_name` IN ('UNITED STATES','CANADA') )
UNION
( SELECT `country_name` FROM `country` as t3
WHERE `country_name` NOT IN ('UNITED STATES','CANADA') 
ORDER BY `country_name` ASC )

 

but this one, first select statement without bracket it displays what I wanted..

 

SELECT `country_name` FROM `country` as t2
WHERE `country_name` IN ('UNITED STATES','CANADA')
UNION
( SELECT `country_name` FROM `country` as t3
WHERE `country_name` NOT IN ('UNITED STATES','CANADA') 
ORDER BY `country_name` ASC )

 

what is the difference between the two? and why the first query doesn't display any row?

 

thanks in advance

[rajivgonsalves]

should be

 

SELECT `country_name` FROM `country`
WHERE `country_name` IN ('UNITED STATES','CANADA')
UNION
( SELECT `country_name` FROM `country`
WHERE `country_name` NOT IN ('UNITED STATES','CANADA') 
ORDER BY `country_name` ASC )

 

the as in the from clause maybe causing the problem it should be a alias so `country` t3 or `country` t3 as per my knowlegde the "as'' keyword I have used when selecting fields not the tables

[DanDaBeginner]

thanks.. theres actually nothing wrong with my query, its just the HeidiSql has some bug or something which results to 0 row, but when I tried it on phpmyadmin it fetch the right rows..

 

 

what could be the problem in heidisql? has anyone encountered a problem like this?

[Barand]

try

SELECT `country_name` FROM `country`
ORDER BY `country_name` IN ('UNITED STATES','CANADA') DESC, `country_name`

[DanDaBeginner]

thats pimp barand, thx. never thought sub query is allowed in ORDER BY statement.. can you lead me to a tutorial that teach this? I've try searching many tutorials and none of them give me atleast a sample of this kind of query..

 

where else does the subquery allowed?

 

thx

[Barand]

It isn't a subquery, it's just a Boolean expression returning 1 or 0 (true or false)

[DanDaBeginner]

ooopss, did I say subquery?? I mean, never thought you can put IN statement on ORDER BY.. can you lead me to a tutorial that does this? been searchin for a tutorial but never found that does this..

[fenway]

try

SELECT `country_name` FROM `country`
ORDER BY `country_name` IN ('UNITED STATES','CANADA') DESC, `country_name`

I've always used "ORDER BY FIELD( myField, ...options-in-reverse-order... ) DESC, myField", because it's more robust -- alphabetical isn't always desirable.  The IN clause just a boolean.

[Barand]

.. can you lead me to a tutorial that does this? been searchin for a tutorial but never found that does this..

 

Afraid I haven't seen one either.