Sort result with empty strings last

67
July 11, 2019, at 9:00 PM

I have this locations table:

+----+-----------+------------+----------+
| id |  country  |   state    |   city   |
+----+-----------+------------+----------+
|  1 |  US       | Georgia    |  Atlanta |
|  2 |  US       | California |          |
|  3 |  US       |            |          |
|  4 |  Canada   |  Ontario   |          |
|  5 |  Canada   |  Manitoba  | Winnipeg |
|  6 |  Canada   |            |          |

I want to create a query but could not build my ORDER BY properly. This is the result that I want:

+----+-----------+------------+----------+
| id |  country  |   state    |   city   |
+----+-----------+------------+----------+
|  6 |  Canada   |            |          |
|  3 |  US       |            |          |
|  4 |  Canada   | Ontario    |          |
|  2 |  US       | California |          |
|  5 |  Canada   | Manitoba   | Winnipeg |
|  1 |  US       | Georgia    | Atlanta  |

Basically, this is the priority that I want to follow:

  1. Country listing. Alphabetical order.
  2. State listing. Alphabetical order.
  3. City listing. Alphabetical order.

This query does not seem to account for empty columns (I am not using NULLs in my locations table):

SELECT * FROM locations
ORDER BY 
    country,state,city  
Answer 1

Try this ORDER BY clause:

SELECT *
FROM locations
ORDER BY
    CASE WHEN state = '' AND city = '' THEN 0
         WHEN city = '' THEN 1
         ELSE 2 END,
    country,
    state,
    city;

This sorting logic places first those records which are missing both state and city. Next follows records missing city only, followed last by records having non empty data for all three fields.

Answer 2

For versions pre 8.0...

DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table
(id SERIAL PRIMARY KEY
,country VARCHAR(20) NOT NULL
,state VARCHAR(20) NULL
,city VARCHAR(20) NULL
);
INSERT INTO my_table VALUES
(1,'US','Georgia','Atlanta'),
(2,'US','California',NULL),
(3,'US',NULL,NULL),
(4,'Canada','Ontario',NULL),
(5,'Canada','Manitoba','Winnipeg'),
(6,'Canada',NULL,NULL);
SELECT id
     , country
     , state
     , city 
  FROM 
     ( SELECT x.*
            , CASE WHEN @prev=country THEN @i:=@i+1 ELSE @i:=1 END i
            , @prev:=country 
         FROM my_table x
            , (SELECT @prev:=null,@i:=0) vars 
        ORDER 
           BY country
            , city
            , state
     ) a 
 ORDER 
    BY i
     , country;
+----+---------+------------+----------+
| id | country | state      | city     |
+----+---------+------------+----------+
|  6 | Canada  | NULL       | NULL     |
|  3 | US      | NULL       | NULL     |
|  4 | Canada  | Ontario    | NULL     |
|  2 | US      | California | NULL     |
|  5 | Canada  | Manitoba   | Winnipeg |
|  1 | US      | Georgia    | Atlanta  |
+----+---------+------------+----------+
READ ALSO
“No module named specutils” although specutils installation went through

“No module named specutils” although specutils installation went through

I am trying to install the Specutils packageI followed these instructions and typed the following command in my Anaconda Prompt terminal:

43
findViewById null on PreferenceFragment

findViewById null on PreferenceFragment

I am using a custom layout in my settings fragment, like below:

35
The right way to find SD-Card path and in work with its files

The right way to find SD-Card path and in work with its files

I am creating a file manager for android and the last thing I have to do is access to sd-card files

43