How to use SQL min () with float?

Question

How to use SQL min () with float?

Navigation

#1 by (1 votes)

1

I have the following database table:

 *Table structure for table 'resultados' */
    DROP TABLE IF EXISTS 'resultados';

    CREATE TABLE 'resultados' (

      'cpf' varchar(14) NOT NULL,
      'nome' varchar(255) DEFAULT NULL,
      'codcurso' int(5) DEFAULT NULL,
      'nota' float DEFAULT NULL,
      PRIMARY KEY ('cpf')

);

/*Data for the table 'resultados' */

insert  into 'resultados'('cpf','nome','codcurso','nota') values 
('123.456.789-01','Alexandre Mie Lopes Mesa',78,4.21),
('123.876.332-72','Maria Ximenes Rosa',1,9.16),
('222.333.444-55','Livia Fernandes Linderberg',43,9.87),
('232.234.789-77','Karol Linderberg',43,8.92),
('289-890-912-76','Pedro Solano Susp',16,4.21),
('345.678.900-37','Amilton Pedro da Silva',78,9.98),
('432.654.987-21','Julio Martinez Silva',21,7.34),
('454.098.123-45','Luiz Henrique de Souza',1,7.35),
('765.123.098-22','Juliana Lopes Alves',22,5.67),
('903.201.871-23','Luiz Peres Lopes',16,8.77),
('987.654.321-10','Ana Alves de Souza',78,9.12);

And I want to select the minimum value when I use only:

SELECT min(nota) AS menor FROM resultados

I get the result (4.21), but if I want to create a table, with the students with the lowest grade I can not get such table

SELECT * FROM resultados WHERE nota = 4.21

I also tried to save this value as float and pass as var but it was not possible, I just could do with the following code:

SELECT * FROM resultados WHERE nota like '" + menorR + "'"

where smallerR = 4.21

I want to understand why note = (float number) did not work

sql jsp

asked by anonymous 25.06.2014 / 10:03

1 answer

Why can not I read a string in the console? Change RG mask [duplicate]

score 1 · Answer 1

This works here:

SELECT *
FROM resultados
WHERE nota = ( SELECT min(nota) AS menor
               FROM resultados )

See in SQLFiddle: link (sometimes stays off the air for a while =( )

The problem is precision: It will not find 4.210000038147 if you only make WHERE nota = 4.21 .

As well said @BorgeB. in the comments of the question, it would be ideal to use DECIMAL instead of FLOAT , because DECIMAL you define precision. For example: three house before the comma and two houses after the comma:

CREATE TABLE 'resultados' (
  'cpf' varchar(14) NOT NULL,
  'nome' varchar(255) DEFAULT NULL,
  'codcurso' int(5) DEFAULT NULL,
  'nota' decimal(3, 2) DEFAULT NULL,
  PRIMARY KEY ('cpf')
);

In decimal(3, 2) , the first number are the boxes before the comma, and the second the boxes after the comma.

Once this is done, the version below should work (credits: @JorgeB.):

SELECT * FROM resultados WHERE nota = 4.21