0

I have searched for this but couldn't find any answer on this particular matter. For my Databases class we have to make a demonstration of the use of some SQL functions on a database we have created. One of those functions is MIN(), while the other is MAX(), among others.

What's happening to me is that when running the command SELECT nome AS mais_barato FROM equipamento HAVING MIN(preço); (names are in portuguese: nome=name, mais_barato=cheaper, equipamento=equipment, preço=price) the output is the following:

+-------------+
| mais_barato |
+-------------+
| ZON Hub     |
+-------------+

Where one would expect (the original table is further bellow, for you to see for yourselves what would be expected):

+-------------+
| mais_barato |
+-------------+
| Modem       |
+-------------+

What is wrong in the statement? When running the SELECT nome AS mais_caro FROM equipamento HAVING MAX(preço); (mais_caro=more_expensive) the output is the same as the one using MIN(preço).

The original table equipamento is (cod is the unique code associated with the equipments):

+-----+----------+--------+
| cod | nome     | preço  |
+-----+----------+--------+
| 152 | ZON Hub  |    120 |
| 228 | PowerBox |    100 |
| 444 | Fibra    |    200 |
| 673 | NetGear  |     70 |
| 779 | Modem    |     50 |
+-----+----------+--------+

I am using mysql Ver 14.14 Distrib 5.5.34, for debian-linux-gnu (i686) using readline 6.2 on Ubuntu 13.10

Thank you very much

RPSR1994
  • 5
  • 3
  • Hi there, I'm brazilian too, just to you know, don't use special character on the fields names the `preço` column should be just `preco` – Jorge Campos Nov 20 '13 at 11:03
  • I know about the special characters, I just use them for the exercise because of the teacher (he's not a very good teacher) – RPSR1994 Nov 20 '13 at 11:07
  • Your query is logically incorrect. The HAVING condition isn't expressed correctly: you woukd use a logical expression. MySQL is misinterpreting the bookean condition. take a look @ http://sqlfiddle.com/#!2/279d2/5 – Lord of the Goo Nov 20 '13 at 11:14
  • Yes, I understand now what I was doing wrong, I will consider seriously onto moving to PostgreSQL due to the facts discusses in the answer given to the question – RPSR1994 Nov 20 '13 at 11:24

1 Answers1

1

Just a wild guess: MySQL is incorrectly interpreting MIN() and MAX() as booleans here.

Your question isn't entirely clear, but I'm guessing you simply want an order by clause:

SELECT nome AS mais_caro
FROM equipamento
ORDER BY preço DESC limit 1

If not, look into subqueries, e.g.:

SELECT nome AS mais_caro
FROM equipamento
WHERE preço = (SELECT MAX(preço) FROM equipamento);

Btw, to learn SQL, you really ought to look into PostgreSQL. The manual is beyond excellent, and it's extremely strict, unforgiving, and informative, when it comes to errors such as the one you did:

denis=# create table test (id serial);
CREATE TABLE
denis=# select id from test having min(id);
ERROR:  argument of HAVING must be type boolean, not type integer
LINE 1: select id from test having min(id);
                                   ^

MySQL, in contrast and as you've found out, accepts a lot of garbage in unless you enable strict mode.

Denis de Bernardy
  • 75,850
  • 13
  • 131
  • 154
  • I wanted only the row with the maximum/minimum value, and I achieved it using subqueries, but I was trying to use the HAVING clause as well, since I haven't used it anywhere else in the exercise, and I got curious as to why MySQL returned the same value with MIN() and MAX(), but with that later addition about PostgreSQL I believeI understood why, thanks a lot – RPSR1994 Nov 20 '13 at 11:12
  • In that case you'd want to `group by … having preço = (SELECT MAX(preço) FROM equipamento)`. Be sure to understand what `group by` does before proceeding. And honestly, run these simple queries in Postgres: any time something is wrong or not crystal clear with them, Postgres will cough an error instead of mindlessly making erroneous assumptions. – Denis de Bernardy Nov 20 '13 at 11:14
  • I'll check PostgreSQL out then, and adapt my code for this exercise only. Btw, how do you enable strict mode in MySQL? – RPSR1994 Nov 20 '13 at 11:17
  • http://stackoverflow.com/questions/5720611/how-to-enable-mysql-strict-mode-globally-and-have-it-stay-on – Denis de Bernardy Nov 20 '13 at 11:37