-2

I have this table.

id  country     col1    col2    col3
1   country1    1   2   3
2   country1    2   2   1
3   country1    1   3   2
4   country2    3   2   2
5   country2    3   3   3
6   country3    3   2   2
7   country3    3   1   1

I am trying to output the last row of each distinct country.

id  country     col1    col2    col3
3   country1    1   3   2
5   country2    3   3   3
7   country3    3   1   1

I have tried various solutions such as:

select distinct(country), col1, col2, col3 from ( SELECT country, col1, col2, col3 from tablename order by id DESC) a limit 1

However, I cannot get the required output.

How can I obtain the latest distinct row for each country?

Thank you!

GMB
  • 216,147
  • 25
  • 84
  • 135
Neil Reardon
  • 65
  • 1
  • 9
  • 1
    Duplicate? [https://stackoverflow.com/questions/5554075/get-last-distinct-set-of-records](https://stackoverflow.com/questions/5554075/get-last-distinct-set-of-records) – Rob Moll Mar 16 '20 at 18:56

3 Answers3

3

You can filter with a correlated subquery:

select t.*
from mytable t
where t.id = (select max(t1.id) from mytable t1 where t1.country = t.country)
GMB
  • 216,147
  • 25
  • 84
  • 135
0

It's simple to get all the last ids with this query:

select max(id) from tablename group by country

and use it to return the rows that you want:

select * from tablename
where id in (select max(id) from tablename group by country)

or with not exists:

select t.* from tablename t
where not exists (
  select 1 from tablename
  where country = t.country and id > t.id
);

See the demo.
Results:

> id | country  | col1 | col2 | col3
> -: | :------- | ---: | ---: | ---:
>  3 | country1 |    1 |    3 |    2
>  5 | country2 |    3 |    3 |    3
>  7 | country3 |    3 |    1 |    1
forpas
  • 160,666
  • 10
  • 38
  • 76
0

Select all the elements in the table and in the WHERE condition filter by a in subquery, in that subquery just group by country: 

 SELECT * 
 FROM tablename 
 WHERE id IN (SELECT MAX(id) FROM tablename  GROUP BY country)
franvergara66
  • 10,524
  • 20
  • 59
  • 101