Not able to perform Union of two specific queries in MariaDB-CodePudding

I am currently learning DBMS. My problem is to find the oldest and the youngest student in the class along with their age and DOB. There is no Age field in the table, I have to use DOB to calculate it. I am able to do it for either the older or for the youngest. But, when I am trying to merge both using UNION, I'm getting an error.

Here are the outputs when I'm running both queries separate.

MariaDB [SocietyDB]> select *, timestampdiff(year, DOB, curdate()) as Age from STUDENT order by DOB limit 1;
 --------- ------- -------- ------------ --------- ------ 
| RollNo  | SName | Course | DOB        | Contact | Age  |
 --------- ------- -------- ------------ --------- ------ 
| AC-1202 | Aditi | BSc CS | 2001-02-23 | NULL    |   20 |
 --------- ------- -------- ------------ --------- ------ 
1 row in set (0.001 sec)

MariaDB [SocietyDB]> select *, timestampdiff(year, DOB, curdate()) as Age from STUDENT order by DOB desc limit 1;
 --------- ------------ -------- ------------ --------- ------ 
| RollNo  | SName      | Course | DOB        | Contact | Age  |
 --------- ------------ -------- ------------ --------- ------ 
| AC-1205 | Aditya Raj | BSc CS | 2003-10-14 | NULL    |   18 |
 --------- ------------ -------- ------------ --------- ------ 
1 row in set (0.001 sec)

Now, here is the error that I'm getting on union operation with both these queries.

MariaDB [SocietyDB]> select *, timestampdiff(year, DOB, curdate()) as Age from STUDENT order by DOB limit 1
    -> union
    -> select *, timestampdiff(year, DOB, curdate()) as Age from STUDENT order by DOB desc limit 1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'union
select *, timestampdiff(year, DOB, curdate()) as Age from STUDENT order...' at line 2

Here is the another try, but, it also gave me error. I'm totally clueless.

MariaDB [SocietyDB]> select *, timestampdiff(year, DOB, curdate()) 'Age' from STUDENT order by DOB limit 1 union select *, timestampdiff(year, DOB, curdate()) from STUDENT order by DOB desc limit 1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'union select *, timestampdiff(year, DOB, curdate()) from STUDENT order by DOB...' at line 1

I know that I should use max and min function for finding the oldest and youngest students,but, it is also giving me error.

Please help me as I'm currently learning DBMS.

Edit:

Here is the STUDENT table that I'm using for this query.

MariaDB [SocietyDB]> select * from STUDENT;
 --------- ------------ ---------- ------------ --------- 
| RollNo  | SName      | Course   | DOB        | Contact |
 --------- ------------ ---------- ------------ --------- 
| AC-1201 | Abhishek   | BSc CS   | 2002-01-14 | NULL    |
| AC-1202 | Aditi      | BSc CS   | 2001-02-23 | NULL    |
| AC-1203 | Aditya Jha | BSc CS   | 2002-02-12 | NULL    |
| AC-1204 | Aditya Kr  | BSc CS   | 2001-12-23 | NULL    |
| AC-1205 | Aditya Raj | BSc CS   | 2003-10-14 | NULL    |
| AC-1206 | Aman       | BSc CS   | 2002-10-01 | NULL    |
| AC-1207 | Amartya    | BSc CS   | 2003-03-12 | NULL    |
| XC-1254 | Shahnwaz   | BSc Chem | 2003-04-03 | NULL    |
| ZC-1234 | Raj Khatri | BSc Chem | 2001-03-30 | NULL    |
 --------- ------------ ---------- ------------ --------- 
9 rows in set (0.001 sec)

MariaDB [SocietyDB]> desc STUDENT;
 --------- ------------- ------ ----- --------- ------- 
| Field   | Type        | Null | Key | Default | Extra |
 --------- ------------- ------ ----- --------- ------- 
| RollNo  | char(7)     | NO   | PRI | NULL    |       |
| SName   | varchar(20) | NO   |     | NULL    |       |
| Course  | varchar(10) | NO   |     | NULL    |       |
| DOB     | date        | YES  |     | NULL    |       |
| Contact | char(10)    | YES  |     | NULL    |       |
 --------- ------------- ------ ----- --------- ------- 
5 rows in set (0.002 sec)

Edit 2:

I modified the query after the tip from @P.Salmon in the comment section. Now, the query is working. But, it is not the perfect solution. It's just a working solution. If anyone has a better solution, kindly post it. It will help in in understanding DBMS in more detail.

MariaDB [SocietyDB]> select *, timestampdiff(year, DOB, curdate()) 'Age' from STUDENT where DOB = (select DOB from STUDENT order by DOB limit 1) union select *, timestampdiff(year, DOB, curdate()) from STUDENT where DOB = (select DOB from STUDENT  order by DOB desc limit 1);
 --------- ------------ -------- ------------ --------- ------ 
| RollNo  | SName      | Course | DOB        | Contact | Age  |
 --------- ------------ -------- ------------ --------- ------ 
| AC-1202 | Aditi      | BSc CS | 2001-02-23 | NULL    |   20 |
| AC-1205 | Aditya Raj | BSc CS | 2003-10-14 | NULL    |   18 |
 --------- ------------ -------- ------------ --------- ------ 
2 rows in set (0.002 sec)

CodePudding user response：

Your original union attempt was very close. You just need to place parentheses around the two queries being unioned so the order by clauses are correctly associated with the inner queries.

(select *, timestampdiff(year, DOB, curdate()) as Age from STUDENT order by DOB asc limit 1)
union all
(select *, timestampdiff(year, DOB, curdate()) as Age from STUDENT order by DOB desc limit 1)

If you want to handle multiple students with the same dob then -

(select *, timestampdiff(year, DOB, curdate()) as Age from STUDENT where DOB = (select max(DOB) from STUDENT))
union all
(select *, timestampdiff(year, DOB, curdate()) as Age from STUDENT where DOB = (select min(DOB) from STUDENT))
order by DOB desc;

If there are multiple students, all with the same dob and all either the oldest or youngest in the class, it will return them all.