Join and order distinct rows from two tables in 1:n relationship

Question

I have a database table r_event with columns:

event_start (date), 
event_stop (date),
insurance_id (integer)

and a table r_insurance with columns:

insurance_id serial primary key,
insurance_name (text)

Each insurance has several events linked by insurance_id.

I am trying to:
SELECT insurance_id, insurance_name - only 1 of each,
and order by the biggest event_stop:
ORDER BY event_stop DESC NULLS LAST -- ??

EXAMPLE

r_insurance (insurance_id, insurance_name)
1 | rca
2 | casco
3 | itp

r_event (insurance_id, event_start, event_stop)
1 | 12.10.2012 | 27.11.2012
1 | 07.05.2012 | 24.06.2012
2 | 21.01.2013 | 14.02.2013

The output should be:

1 | casco -- cause it has the event with the biggest event_stop  
2 | rca   -- cause it has the 1st event_stop after the biggest event_stop  
3 | itc   -- cause it doesn't have events

I edited my first draft, I want them to be ordered descending by the event with the biggest event_stop and NULLS LAST.

[What have you tried?](http://mattgemmell.com/2008/12/08/what-have-you-tried/) — GarethD, Jun 07 '12 at 11:32
if there is many event to one insurance with event should be order by? — Arion, Jun 07 '12 at 11:33
"only one" := only one of which? For which a corresponding r_event record exists (in a particular range of dates) ? — wildplasser, Jun 07 '12 at 11:34
i noticed ppl say it can be done with GROUP BY but i dont quite know what does it do and how — Pacuraru Daniel, Jun 07 '12 at 11:35
Your explanation isn't very clear. In such cases it is useful to give example input data, and example output data; to show what you have, what you want, and how you want to get there. — MatBailie, Jun 07 '12 at 11:36
Why wouldn't 2|rca be the first one in the output? Isn't February 14, 2013 the biggest event.stop? — ganders, Jun 07 '12 at 14:03
I took the liberty to largely rewrite your question to clarify. Please edit if I didn't get it right. — Erwin Brandstetter, Jun 07 '12 at 21:53

score 2 · Accepted Answer · edited May 23 '17 at 10:34

SELECT i.insurance_id, i.insurance_name, max(e.event_stop) latest_stop
FROM   r_insurance i
LEFT   JOIN r_event e USING (insurance_id)
GROUP  BY 1, 2
ORDER  BY latest_stop DESC NULLS LAST;

The LEFT JOIN is essential to avoid losing rows from r_insurance that do not have related rows in r_event - itc in your example.
ORDER BY has do be DESC. Also, no comma before NULLS LAST.

In PostgreSQL 9.1, the primary key covers all non-aggregated columns of the table in the SELECT list, so you can simplify (more details here):

SELECT i.insurance_id, i.insurance_name, max(e.event_stop) latest_stop
FROM   r_insurance i
LEFT   JOIN r_event e USING (insurance_id)
GROUP  BY 1
ORDER  BY latest_stop DESC NULLS LAST;

Demo on sqlfiddle.

score 1 · Answer 2 · answered Jun 07 '12 at 11:53

"it can be done with GROUP BY but i dont quite know what does it do and how"

Group by takes a relation and divides it up into partitions with all of the rows having the same value. From there, you will get a single row from each partition in the result relation; with aggregate functions taken over each partition instead of the whole thing. You could probably formulate something like:

SELECT
 insurance_id,
 insurance_name,
 max(stop) max_stop
FROM r_event JOIN r_insurance
 ON r_event.insurance_id = r_insurance.insurance_id
GROUP BY insurance_id, insurance_name
ORDER BY max_stop, NULLS LAST

Join and order distinct rows from two tables in 1:n relationship

EXAMPLE

The output should be:

2 Answers2