Kann man einen JOIN mit ORDER BY und LIMIT eingrenzen?
Mal kleines Beispiel:
table_a
id | name
table_b
id | aid | datum
Bisheriges Query
SELECT table_a.name, table_b.datum FROM table_a
JOIN table_b ON table_a.id = table_b.aid
In table_b können unter Umständen mehrere Einträge mit gleicher aid vorliegen aber unterschiedliche datum-Werte. Jetzt würde ich gerne eine Abfrage haben die mir nur die Zeilen mit den neuestem datum-Wert liefert.
Geht das mit JOIN oder muss ich das über ein Subselect lösen?
Ich hab schon Beispiele mit NATURAL JOIN gefunden mit dem es scheinbar funktionieren soll aber da bin ich irgendwie an der Syntax gescheiert
für die letzten N Datum-Werte, also die letzten N aktuellen:
test=# select * from (select table_a.name, table_b.datum, row_number() over (partition by name order by datum desc) FROM table_a JOIN table_b ON table_a.id = table_b.a_id) foo whererow_number < 3;
name | datum | row_number
-------+------------+------------
name1 | 2009-01-04 | 1
name1 | 2009-01-03 | 2
name2 | 2009-05-01 | 1
(3 rows)
Die obige Lösung klappt leider nur wenn ich aus Tabelle B nur das Datumsfeld benötige, in meinem reellen Projeklt verknüpfe ich aber 3 Tabellen mit je ~20 Spalten.
Beispiel:
test=# SELECT * FROM tbl_a;
id | name | ort
----+-------+-----
1 | Name1 | Ort
2 | Name2 | Ort
(2 rows)
test=# SELECT * FROM tbl_b;
id | a_id | datum | extra
----+------+------------+-----------
1 | 1 | 2009-01-01 | Apfel
2 | 1 | 2009-01-02 | Birne
3 | 1 | 2009-01-03 | Kartoffel
4 | 2 | 2009-01-02 | Möhre
5 | 2 | 2009-01-04 | Pfirsich
(5 rows)
Ziel:
test-# *MIGHTY QUERY*;
name | ort | datum | extra
-------+-----+------------+-----------
Name1 | Ort | 2009-01-03 | Kartoffel
Name2 | Ort | 2009-01-04 | Pfirsich
(2 rows)
test=# select * from table_a;
id | name | ort
----+-------+-----
1 | name1 | Ort
2 | name2 | Ort
(2 rows)
test=# select * from table_b;
id | a_id | datum | extra
----+------+------------+-----------
1 | 1 | 2009-01-01 | Apfel
2 | 1 | 2009-01-02 | Birne
3 | 1 | 2009-01-03 | Kartoffel
5 | 2 | 2009-01-05 | Pfirsisch
4 | 2 | 2009-01-02 | Moehre
(5 rows)
test=# select name, ort, datum, extra from (select table_a.name, table_a.ort, table_b.datum, table_b.extra, row_number() over (partition by name order by datum desc) FROM table_a JOIN table_b ON table_a.id = table_b.a_id) foo where row_number =1;
name | ort | datum | extra
-------+-----+------------+-----------
name1 | Ort | 2009-01-03 | Kartoffel
name2 | Ort | 2009-01-05 | Pfirsisch
(2 rows)
test=# SELECT distinct on (name) table_a.name, table_a.ort, table_b.datum, table_b.extra, MAX(table_b.datum) FROM table_b JOIN table_a ON table_b.a_id = table_a.id GROUP BY table_a.name, table_a.ort, table_b.datum, table_b.extra order by name, datum desc;
name | ort | datum | extra | max
-------+-----+------------+-----------+------------
name1 | Ort | 2009-01-03 | Kartoffel | 2009-01-03
name2 | Ort | 2009-01-05 | Pfirsisch | 2009-01-05
(2 rows)