Select the two highest values of a tuple

Asked

Viewed 1,579 times

3

I have an appointment 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 fields "v1", "v2" and "v3"?

Result thus:

ID    | Maior_v1  | Maior_v2 | 
54384 | 8         | 2.55     |
54385 | 5.6       | 3.24     |

Are there any way? How to do?

mysql sql

3 answers

2

Mysql has a function called GREATEST which returns the highest value in a series of columns.

The problem in this case is to find the second highest value. My idea was to exchange the highest value for the lowest value with LEAST through 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.

2


It is possible with the use of functions GREATEST andLEAST. View 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

Sqlfiddle

  • I liked the logic of catching the smallest of the biggest.

1

I believe there is some more performative way, but with this set of cases it is possible to arrive at 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)

Example in Sqlfiddle

  • 1

    Maybe extract the logic to determine the larger ones in a process ?

  • It has the advantage of being logical comparisons in the same record, without nested loops. But still I found it kind of ugly query

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.