sql getting the minimum and maximum result in same query-CodePudding

From the years table below, how would I be able to get the rows which have the minimum and maximum years? For example, the minimum year in the years table is 1776 and the maximum year is 2021. Therefore, the resulting table should have the row with the year 1776 and the 3 rows with 2021.

Years table

 -------- ------ 
| name   | year |
 -------- ------ 
| name 1 | 1776 |
| name 2 | 1905 |
| name 3 | 2000 |
| name 4 | 2021 |
| name 5 | 2021 |
| name 6 | 2021 |
 -------- ------

Desired result

     -------- ------ 
    | name   | year |
     -------- ------ 
    | name 1 | 1776 |
    | name 4 | 2021 |
    | name 5 | 2021 |
    | name 6 | 2021 |
     -------- ------

CodePudding user response：

Find the minimum and maximum year and joined it to the main table

SELECT name,year
FROM

  (SELECT min(year) AS min_year,MAx(year) AS max_year
   FROM years) t1
JOIN years t2 ON t1.min_year = t2.year OR t1.max_year = t2.year

db<>fiddle