Topic: 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

Code: [Select]

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

Code: [Select]

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

Code: [Select]

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

Code: [Select]

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

[DanDaBeginner]

thats pimp barand, thanks. 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?

thanks

[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

Code: [Select]

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.