I have a query that displays the following result:
ID | v1 | v2 | v3 |
54384 | 2.4 | 8 | 2.55 |
54385 | 5.6 | 1 | 3.24 |
How to do, in a SELECT , the selection of only the two largest values between the "v1", "v2" and "v3" fields?
Results like this:
ID | Maior_v1 | Maior_v2 |
54384 | 8 | 2.55 |
54385 | 5.6 | 3.24 |
It is possible to use the functions GREATEST and LEAST . See the documentation here .
SELECT dados.id, max1.valor as maior_v1, max2.valor as maior_v2
FROM dados
INNER JOIN (
SELECT id, GREATEST(v1, v2, v3) as valor FROM dados
) as max1
ON max1.id = dados.id
INNER JOIN (
SELECT id, LEAST(GREATEST(v1, v2), GREATEST(v2, v3), GREATEST(v1, v3)) valor FROM dados
) as max2
ON max2.id = dados.id
MySql has a function called GREATEST that returns the highest value in a series of columns.
The problem here is finding the second largest value. My idea was to exchange the highest value at the lowest value with LEAST via a subquery
SELECT id, maior,
GREATEST(
if(v1 = m.maior, LEAST(v1, v2, v3), v1),
if(v2 = m.maior, LEAST(v1, v2, v3), v2),
if(v3 = m.maior, LEAST(v1, v2, v3), v3)
) as maior2
FROM
(
SELECT *, GREATEST(v1, v2, v3) as maior FROM new_table
) as m;
Example in sqlfiddle .
I believe that there is some way more performative, but with this set of cases it is possible to reach the result:
Select
Notas.ID,
NOTAS_V1.MaiorV1,
NOTAS_V1.MaiorV2
FROM
Notas
INNER JOIN
(SELECT
N1.ID,
CASE
WHEN (N1.V1 > N1.V2) AND (N1.V1 > N1.V3) THEN N1.V1
WHEN (N1.V2 > N1.V1) AND (N1.V2 > N1.V3) THEN N1.V2
ELSE N1.V3
END as MaiorV1,
CASE
WHEN (N1.V1 > N1.V2) AND (N1.V1 > N1.V3) THEN
CASE
WHEN (N1.V2 > N1.V3) THEN N1.V2
ELSE N1.V3
END
WHEN (N1.V2 > N1.V1) AND (N1.V2 > N1.V3) THEN
CASE
WHEN (N1.V1 > N1.V3) THEN N1.V1
ELSE N1.V3
END
ELSE
CASE
WHEN (N1.V1 > N1.V2) THEN N1.V1
ELSE N1.V2
END
END as MaiorV2
FROM NOTAS N1
) AS NOTAS_V1 ON (Notas.ID = NOTAS_V1.ID)