SQL : How to select the most recent value by country

Question

I have a table with 3 columns : day, country, value. There are many values by country with different dates. For example :

  DAY            COUNTRY       VALUE 
  04-SEP-19      BELGIUM        2124
  15-MAR-19      BELGIUM        2135
  21-MAY-19      SPAIN          1825
  18-JUL-19      SPAIN          1724
  26-MAR-19      ITALY          4141

I want to select the most recent value by country. For example :

  DAY            COUNTRY       VALUE 
  04-SEP-19      BELGIUM        2124
  18-JUL-19      SPAIN          1724
  26-MAR-19      ITALY          4141

What is the sql query I can use?

Thank you for your help.

You can see the answer here: https://stackoverflow.com/questions/19432913/select-info-from-table-where-row-has-max-date — Dani Toker, Feb 03 '20 at 10:59

NikNik · Answer 1 · 2020-02-03T15:57:32.217

0

Let's suppose that the type of day column is date. In the subquery, you can find the tuple of (country, max date) and to add the value, you can join as mentioned in the comments or use IN

SELECT DISTINCT day, country, value
FROM yourTable
WHERE (country, day)
in (
    SELECT country, MAX(day) as day
    FROM yourTable
    GROUP BY country, value
)

edited Feb 03 '20 at 15:57

answered Feb 03 '20 at 11:03

NikNik

2,191
2
15
34

@Laszlo You can add distinct in the first query – NikNik Feb 03 '20 at 15:57

baretomas · Answer 2 · 2020-02-03T14:48:41.263

0

I understand the problem as you want the most recent value for all countries, as the country can repeat in the table(?):

    select distinct t1.DAY, t1.COUNTRY, t1.VALUE
    FROM day_test t1 
    inner join day_test t2 on t1.day in 
            (select max(day) from day_test t3 where t1.country = t3.country ) 
            and t1.country = t2.country

I tested it and it works.

edited Feb 03 '20 at 14:48

answered Feb 03 '20 at 11:13

baretomas

1
1

No need to do GROUP BY when no aggregate functions are used, simply do SELECT DISTINCT. – jarlh Feb 03 '20 at 12:38
Ive amended my answer. Thanks for the heads up. Apparently OP wanted a answer more complicated and platform-dependent... strange. – baretomas Feb 03 '20 at 14:49

score 0 · Accepted Answer · answered Feb 03 '20 at 11:20

You can use the row_number() window function (if your DBMS supports it)).

SELECT x.day,
       x.country,
       x.value
       FROM (SELECT t.day,
                    t.country,
                    t.value,
                    row_number() OVER (PARTITION BY t.country
                                       ORDER BY t.day DESC) rn
                    FROM elbat t) x
       WHERE x.rn = 1;

score 0 · Answer 4 · answered Feb 03 '20 at 11:22

Another way of doing this is using a window function (SQL Server, MySQL8 etc)

e.g.

ROW_NUMBER() OVER ( PARTITION BY COUNTRY ORDER BY CONVERT(DATE, [Day]) DESC )

Then just filter to where this function returns 1

full example:

WITH TestData
  AS ( SELECT '04-SEP-19' AS [Day], 'BELGIUM' AS [COUNTRY], 2124 AS [VALUE]
       UNION
       SELECT '15-MAR-19' AS [Day], 'BELGIUM' AS [COUNTRY], 2135 AS [VALUE]
       UNION
       SELECT '21-MAY-19' AS [Day], 'SPAIN' AS [COUNTRY], 1825 AS [VALUE]
       UNION
       SELECT '18-JUL-19' AS [Day], 'SPAIN' AS [COUNTRY], 1724 AS [VALUE]
       UNION
       SELECT '26-MAR-19' AS [Day], 'ITALY' AS [COUNTRY], 4141 AS [VALUE] ),
     TestDataRanked
  AS ( SELECT *,
              ROW_NUMBER() OVER ( PARTITION BY COUNTRY ORDER BY CONVERT(DATE, [Day]) DESC ) AS SelectionRank
       FROM   TestData )
SELECT [Day],
       COUNTRY,
       [VALUE]
FROM   TestDataRanked
WHERE  SelectionRank = 1;

score -1 · Answer 5 · answered Feb 03 '20 at 11:03

-1

You can use the following query: Just replace the TABLE_NAME with the name of your table.

 SELECT 
    COUNTRY,
    VALUE,
    MAX(DATE) AS "MostRecent"
FROM TABLE_NAME
GROUP BY COUNTRY;

answered Feb 03 '20 at 11:03

Sumit Singh

487
5
19

1

You cannot leave the value there, you would get: ...is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause – Dani Toker Feb 03 '20 at 11:06

SQL : How to select the most recent value by country

5 Answers5