sql getting the minimum and maximum result in same query

user_17346283 0
Viewed 31

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 |
    +--------+------+
sql
1 Answers

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

user_150195360
Related