I'm trying to select 2 oldest females and 2 oldest males using 1 query. The union keeps giving me a syntax error near "(". Both queries work independantly but after union I get error.
-- create a table
CREATE TABLE students (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
gender TEXT NOT NULL,
age INTEGER NOT NULL
);
-- insert some values
INSERT INTO students VALUES (1, 'Ryan', 'M', 23);
INSERT INTO students VALUES (2, 'Joanna', 'F', 22);
INSERT INTO students VALUES (3, 'Alex', 'F', 25);
INSERT INTO students VALUES (4, 'Ted', 'M', 21);
INSERT INTO students VALUES (5, 'June', 'F', 26);
INSERT INTO students VALUES (6, 'Rose', 'F', 24);
INSERT INTO students VALUES (7, 'Jack', 'M', 25);
-- select * from students;
SELECT * FROM
(SELECT name FROM students WHERE GENDER = 'F' ORDER BY age DESC LIMIT 2)
UNION
(SELECT name FROM students WHERE GENDER = 'M' ORDER BY age DESC LIMIT 2);
CodePudding user response：
Your online compliler uses not MySQL but SQLite!
Execute select sqlite_version(); - the output is '3.31.1'.
Use this:
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY gender ORDER BY age DESC) rn
FROM students
)
SELECT name
FROM cte
WHERE rn <= 2;
This code is correct for SQLite.
PS. Add ORDER BY if needed.
CodePudding user response：
For SQLite both unioned queries must be used as subqueries with an external SELECT clause and you can use an ORDER BY clause at the end which will be applied to the resultset of the union and will put all Fs at the top because they are alphabetically less than the Ms:
SELECT * FROM (SELECT * FROM students WHERE gender = 'F' ORDER BY age DESC LIMIT 2)
UNION
SELECT * FROM (SELECT * FROM students WHERE gender = 'M' ORDER BY age DESC LIMIT 2)
ORDER BY gender, age;
See the demo.
CodePudding user response：
Use this:
(SELECT name FROM students WHERE GENDER = 'F' ORDER BY age DESC LIMIT 2)
UNION
(SELECT name FROM students WHERE GENDER = 'M' ORDER BY age DESC LIMIT 2)