I want to use ORDER BY clause but still have one specific entry on the top.
I thought this might do it but Vertica is ignoring the ORDER BY clause on the second query:
(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)
Resulting in
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 is
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;
Best Answer
The query needs
ORDER BY
in the end, not in the subqueries (and note that the parentheses are optional there, at least in standard SQL, not sure if Vertica requires them):But since you are grabbing all the data from the same table, there is no need for the
UNION
and the subqueries: