We have the student table with the following fields:
doc_est int(30)
nom_est varchar(30)
ape_est varchar(30)
edad_est int(11)
They ask us to perform the following function:
• Show the name and age of the youngest student.
I have not been able to solve it. Can someone help me?
You have to do a INNER JOIN with the same table, but using the function MIN to get the minimum age in the table. In this way the query will return only students whose age is equal to the minimum age (there can be several if more than one has the same age).
SELECT
E.nom_est,
E.edad_est
FROM estudiante E
INNER JOIN
(
SELECT
MIN(edad_est) AS EDAD
FROM estudiante
) EDAD_MINIMA
ON E.edad_est = EDAD_MINIMA.EDAD;
Source: Reply in "MySQL Select row with lowest value in column" (Stack Overflow)
You can do:
SELECT nom_est, edad_est
FROM estudiante
WHERE edad_est = (SELECT MIN(edad_est) FROM estudiante);
or
SELECT nom_est, edad_est
FROM estudiante
WHERE edad_est IN (SELECT MIN(edad_est) FROM estudiante);
If what you are trying to do is create a function that always returns the lowest age, you should consider the following, if you do a INTO command within a variable, it only expects a value , so if there are two estudiantes with the same age the function will not return any result.
Considering
One function that I just tried and it works is the following:
CREATE DEFINER='root'@'localhost' FUNCTION 'estujoven'() RETURNS int(11)
NO SQL
BEGIN
DECLARE edadest INT(30);
SET edadest = 0;
SELECT
E.edad_est into edadest
FROM estudiante E
INNER JOIN
(
SELECT
MIN(edad_est) AS edad_min
FROM estudiante
) tabla_filtro
ON E.edad_est = tabla_filtro.edad_min;
RETURN edadest;
END;
Result:
None of the answers gives a solution because what you want to create is a function that when you call it shows the name of the student that is a string of characters and the minimum age of a student which is an integer.
in other words what you want to show when calling the function is:
nom_Est age_dEst
maria 17
I think what you're looking for is something like that in MySQL :
select nom_est, min(edad_est) from estudiante group by nom_est limit 1;
The trick of grouping nom_est allows you to display the name, since min is a group function.
Another way would be (NOT RECOMMENDED) :
select nom_est, edad_est from estudiante order by edad_est limit 1;
The limit 1 , forces the query to bring a single value and as ordered, will only bring the least.