CODEWITHSUNDEEP
mysqlsql
tklustig
tklustig

Reputation: 503

SQL-Query for maximum AND minimum

What's the right (MySql)- query in order to get the highest AND the smallest value of a table?I got this, but that's faulty:

SELECT * FROM temperaturs ASC LIMT 3  UNION SELECT * from temperaturs  ORDER BY `Temperatur_Celsius` DESC LIMIT 3;

This is faulty,too:

SELECT * FROM tabelle_name ORDER BY spalte1 DESC LIMIT k UNION SELECT * FROM tabelle_name ORDER BY spalte1 ASC LIMIT k

Upvotes: 0

Views: 101

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Your query just needs parentheses:

(SELECT * FROM temperaturs ORDER BY `Temperatur_Celsius` ASC LIMIT 3)
UNION ALL
(SELECT * from temperaturs ORDER BY `Temperatur_Celsius` DESC LIMIT 3);

Note: I switched from UNION to UNION ALL. This will allow multiple rows if the table has fewer than 6 rows. In general, UNION is a bad practice because it incurs the overhead of removing duplicates.

MySQL needs parentheses when subqueries to a UNION/UNION ALL use ORDER BY or LIMIT.

Upvotes: 2

Related Questions