mit Zwischenschritt zum besseren Verständniss...
[code]
edb=*# select * from creativeone;
visit_id | geraete_id | datum | typ_id
----------+------------+---------------------+--------
1 | 1 | 2021-01-01 00:00:00 | 1
2 | 2 | 2021-01-01 00:00:00 | 1
3 | 1 | 2021-01-02 00:00:00 | 2
4 | 1 | 2021-01-04 00:00:00 | 2
5 | 1 | 2021-01-08 00:00:00 | 3
6 | 3 | 2021-01-09 00:00:00 | 1
7 | 3 | 2021-01-10 00:00:00 | 3
(7 rows)
edb=*# with tmp as (select geraete_id, max(datum) datum from creativeone group by geraete_id) select * from creativeone inner join tmp using (geraete_id,datum);
geraete_id | datum | visit_id | typ_id
------------+---------------------+----------+--------
2 | 2021-01-01 00:00:00 | 2 | 1
1 | 2021-01-08 00:00:00 | 5 | 3
3 | 2021-01-10 00:00:00 | 7 | 3
(3 rows)
edb=*# with tmp as (select geraete_id, max(datum) datum from creativeone group by geraete_id), tmp2 as (select typ_id from creativeone inner join tmp using (geraete_id,datum)) select typ_id, count(1) from tmp2 group by typ_id;
typ_id | count
--------+-------
1 | 1
3 | 2
(2 rows)
edb=*#
[/code]
