JOIN mit ORDER BY und LIMIT - geht das?

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 :confused:

Ohne es getestet zu haben:

SELECT table_a.name, MAX(table_b.datum) FROM table_b
JOIN table_a ON table_b.id = table_a.aid
GROUP BY table_a.name



test=# select * from table_a;
 id | name
----+-------
  1 | name1
  2 | name2
(2 rows)

test=# select * from table_b;
 id | a_id |   datum
----+------+------------
  1 |    1 | 2009-01-01
  2 |    1 | 2009-01-02
  3 |    1 | 2009-01-03
  4 |    1 | 2009-01-04
  5 |    2 | 2009-05-01
(5 rows)

test=# select * from (select table_a.name, table_b.datum, row_number() over (partition by name) FROM table_a JOIN table_b ON table_a.id = table_b.a_id) foo where row_number < 3;
 name  |   datum    | row_number
-------+------------+------------
 name1 | 2009-01-01 |          1
 name1 | 2009-01-02 |          2
 name2 | 2009-05-01 |          1
(3 rows)

Andreas

Nachtrag:

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)

Andreas

Ja. Stimmt. Wenn nur das maximale Datum gewollt ist. Ich habe das LIMIT gesehen und dachte mir, er will die letzten N Werte.

Andreas

Ja ich dachte dass ich die einzelnen Ergebnisse des JOINs auf 1 limitiere. Also ich brauch nur den Eintrag mit dem letzten (neuesten) Datum.

THx euch beiden … ich werd mal versuchen das ganze einzubauen :slight_smile:

Jetzt muss ich doch nochmal fragen :confused:

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)

g

Andreas

Und als Pre-8.4 - Version:

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)

Andreas