Home / Questions / 423272
Accepted
Antonio Ruiz
Asked: 2022-01-26 04:30:31 +0800 CST 2022-01-26 04:30:31 +0800 CST

How do I collect unique values ​​in MySQL depending on the year?

  • 772

I have a table where the values ​​can be repeated in each year (or not), I need to collect only the references of the last year with their values ​​in the event that they are repeated in different periods.

Period Reference Value1 Value2
2020 REF1 C1 C2
2020 REF2 C2 C3
2020 REF3 C0 C3
2021 REF1 C11 C22
2021 REF2 C22 C33
2021 REF4 C0 C3

I want to get this unique filtering taking into account the closest year:

Period Reference Value1 Value2
2020 REF3 C0 C3
2021 REF1 C11 C22
2021 REF2 C22 C33
2021 REF4 C0 C3

How could I do it?

Here is my last test, but it keeps thinking for more than 3 minutes and finally I cancel it, I need to do it in a few seconds.

SELECT df.* FROM datosfase df,
(
SELECT MAX(PERIODO) AS PERIODO, REFERENCIA
FROM datosfase 
GROUP BY REFERENCIA
ORDER BY PERIODO DESC
) t1
WHERE df.PERIODO = t1.PERIODO AND df.REFERENCIA= t1.REFERENCIA
mysql

1 Answers

  1. Best Answer

    Option 1

    • We create a query to get the periodo largest for eachreferencia

      SELECT
     MAX(periodo) AS periodo,
     referencia
FROM datosfase
GROUP BY referencia

    • If we move this query to a derived table to do a JOINagainst all the records using the values ​​obtained as a filter, we will obtain the expected result

    Solution:

    SELECT A.*
FROM datosfase AS A
    INNER JOIN (
        SELECT
            MAX(periodo) AS periodo,
            referencia
        FROM datosfase
        GROUP BY referencia
     ) AS B
     ON B.periodo = A.periodo
     AND B.referencia = A.referencia

    Demo

    Option 2

    • We make a LEFT JOINof the table with itself where the referenciais equal and the periodois less than the joined.

      • Only records whose periodois the largest will NOT have a record in the joined table ( eg: null) .

    • Then it only remains to filter the records where the joined table isNULL

        SELECT A.*
  FROM datosfase A
      LEFT JOIN datosfase B
        ON A.referencia = B.referencia
        AND A.periodo < B.periodo
  WHERE B.periodo IS NULL;

    Demo

    • 5