PostgreSQL GROUP BY: SELECT column on MAX of another WHERE a third column = x

Question

Let's suppose we have two tables in PostgreSQL:

Table "citizens"

country_ref   citizen_name    entry_date
-----------------------------------------------------
0             peter           2013-01-14 21:00:00.000
1             fernando        2013-01-14 20:00:00.000
0             robert          2013-01-14 19:00:00.000
3             albert          2013-01-14 18:00:00.000
2             esther          2013-01-14 17:00:00.000
1             juan            2013-01-14 16:00:00.000
3             egbert          2013-01-14 15:00:00.000
1             francisco       2013-01-14 14:00:00.000
3             adolph          2013-01-14 13:00:00.000
2             emilie          2013-01-14 12:00:00.000
2             jacques         2013-01-14 11:00:00.000
0             david           2013-01-14 10:00:00.000

Table "countries"

country_id     country_name   country_group
-------------------------------------------
0              england        0
1              spain          0 
2              france         1
3              germany        1

Now I want to obtain the last entered citizen on the "citizens" table for each country of a given country_group.

My best try so far is this query (Let's call it Query_1) :

SELECT country_ref, MAX(entry_date) FROM citizens 
LEFT JOIN countries ON country_id = country_ref 
WHERE country_group = 1 GROUP BY country_ref

Output:

country_ref   max
---------------------------------
3             2013-01-14 18:00:00
2             2013-01-14 17:00:00

So then I could do:

SELECT citizen_name FROM citizens WHERE (country_ref, entry_date) IN (Query_1)

... which will give me the output I'm looking for: albert and esther.

But I'd prefer to achieve this in a single query. I wonder if it's possible?

score 8 · Accepted Answer · edited May 23 '17 at 12:23

This should be simplest and fastest:

SELECT DISTINCT ON (i.country_ref)
       i.citizen_name
FROM   citizens  i
JOIN   countries o ON o.country_id = i.country_ref
WHERE  o.country_group = 1
ORDER  BY i.country_ref, i.entry_date DESC

You can easily return more columns from both tables by simply adding them to the SELECT list.
SQL Fiddle.

Details, links and explanation in this related answer:

Select first row in each GROUP BY group?

score 4 · Answer 2 · answered Feb 10 '13 at 23:32

SELECT citizen_name, 
       country_ref, 
       entry_date
from (
  SELECT cit.citizen_name, 
         cit.country_ref, 
         MAX(cit.entry_date) over (partition by cit.country_ref) as max_date,
         cit.entry_date
  FROM citizens cit
    LEFT JOIN countries cou ON cou.country_id = cit.country_ref 
  WHERE cou.country_group = 1 
) t
where max_date = entry_date

SQLFiddle demo: http://www.sqlfiddle.com/#!12/50776/1

score 2 · Answer 3 · answered Feb 10 '13 at 23:10

2

Why don't you simply:

SELECT citizen_name FROM citizens WHERE (country_ref, entry_date) IN (
    SELECT country_ref, MAX(entry_date) FROM citizens 
    LEFT JOIN countries ON country_id = country_ref 
    WHERE country_group = 1 GROUP BY country_ref
)

It might not be the best plan, but it depends on many factors, and it is simple to write.

answered Feb 10 '13 at 23:10

Yes, that's what I'm actually doing so far (see that I wrote SELECT citizen_name FROM citizens WHERE (country_ref, entry_date) IN (Query_1) in my question) but I would like to do it in a single query if possible... – Fracu Feb 10 '13 at 23:24
1

@Francisco: that **is** a single query – Feb 10 '13 at 23:25
Sorry about that, I meant to say I'm trying to avoid nested queries or the use or subqueries. – Fracu Feb 12 '13 at 20:27
Why? There is no logical reason to avoid them. – Feb 12 '13 at 20:32
I was thinking that my first query was complex enough to then be used inside another query. – Fracu Feb 12 '13 at 23:44

PostgreSQL GROUP BY: SELECT column on MAX of another WHERE a third column = x

3 Answers3

Linked