2

I have a table with list of all country names along with amount field. What I need is to display the country name with amount not equal to 0 at top order in ascending order of name and then display the country with amount value 0.

Name          Amount
Afghanistan    0
Argentina      0
China          0.1
Crotia         0
Hongkong       0.08
India          0.2
Singapore      0.007
Taiwan         0.22

Required Output:

Name          Amount
China          0.1
Hongkong       0.08
India          0.2
Singapore      0.007
Taiwan         0.22
Afghanistan    0
Argentina      0
Crotia         0

I have tried following till now but its not working.

select * from countries where amount !=0 ORDER by name ASC 
UNION ALL
SELECT * from countries where amount == 0 ORDER BY name ASC
nas
  • 2,289
  • 5
  • 32
  • 67

4 Answers4

2

You can order by a custom CASE expression:

SELECT *
FROM countries
ORDER BY
    CASE WHEN amount > 0 THEN 0 ELSE 1 END,    -- places zeroes last
    amount                                     -- then order by the amount
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • This would order by amount, but I believe based on the output given by the thread starter he wanted the result to be ordered by name with amount > 0 and place those with 0 amount at the bottom. – Learning Aug 09 '17 at 03:09
2
SELECT *
  FROM countries
  ORDER BY (Amount=0), Name
Sal
  • 1,307
  • 1
  • 8
  • 16
0

I think mysql treats 0 as null. Typically null always returns false with any comparison, so instead of checking if the value is 0 check if its not null. So select * from countries where amount is not null order by name asc union all select * from countries where amount is null

mancini0
  • 4,285
  • 1
  • 29
  • 31
0

Maybe you are looking for something like?

SELECT * FROM countries 
ORDER BY 
CONCAT(CASE amount WHEN 0 THEN "Z" ELSE "A" END,`name`) asc;
Learning
  • 163
  • 1
  • 11