Vertica联合与订购

Question

我想使用ORDER子句，但仍然有一个特定的条目在顶部。我认为这样可以做到，但是Vertica忽略了第二个查询中的ORDER子句：

(SELECT country_id, country_name 
FROM country_dim 
WHERE country_dim.iso_country_code LIKE 'US')
UNION 
(SELECT country_id, country_name 
FROM country_dim 
WHERE country_dim.iso_country_code NOT LIKE 'US' 
ORDER BY country_name ASC)

导致

12  United States of America
10  Germany
5   Brazil
6   Canada
7   China
8   France
4   Algeria
3   Aland Islands
2   Albania
8   Denmark
11  United Arab Emirates
13  Central African Republic
-1  Unknown
14  Svalbard and Jan Mayen

DDL是

CREATE TABLE country_dim
(
    country_id int NOT NULL,
    iso_country_code char(2) NOT NULL,
    country_name varchar(512) NOT NULL,
    create_ts timestamptz,
    update_ts timestamptz
);

ALTER TABLE country_dim ADD CONSTRAINT country_dim_pk PRIMARY KEY (country_id) DISABLED;

Answer 1

查询最后需要ORDER BY，而不是子查询(注意括号是可选的，至少在标准SQL中是可选的，不确定Vertica是否需要它们)：

( SELECT country_id, country_name 
  FROM country_dim 
  WHERE iso_country_code LIKE 'US')
UNION 
( SELECT country_id, country_name 
  FROM country_dim 
  WHERE iso_country_code NOT LIKE 'US')     -- without ORDER BY
ORDER BY 
    CASE WHEN iso_country_code LIKE 'US' THEN 0 ELSE 1 END ASC,
    country_name ASC ; 

但是，由于您从同一个表中获取所有数据，所以不需要UNION和子查询：

SELECT country_id, country_name 
FROM country_dim 
ORDER BY 
    CASE WHEN iso_country_code LIKE 'US' THEN 0 ELSE 1 END ASC,
    country_name ASC ;