Ausgabe von bestimmten Tabellenzeilen

Hallo zusammen,

ich habe ein Problem und hoffe jemand von euch kann mir bitte weiterhelfen. :slight_smile:
Ich habe eine Tabelle Person und eine Tabelle Telefonnummer. Die Personen-ID ist ein Fremdschlüssel der Telefonnummern-Tabelle um, die zugehörige(n) Telefonnummer(n) zu löschen, sobald die Person gelöscht wird (cascade on delete).

Soweit so gut…
Wie kann ich jetzt allerdings eine Abfrage bauen, welche mir beispielsweise alle Personen mit der ersten Telefonnummer ausgibt? Erste Telefonnummer heißt hier, die Nummer, welche als erstes eingegeben wurde. Außerdem sollte die Lösung auch für folgende Fragestellung gehen: Gebe alle Personen mit den ersten beiden Telefonnummer aus.

Ich weiß nicht so recht, wie ich die Nummer in der Tabelle abspeicher, so dass ich später genau sagen kann, dass war die erste, dass die zweite usw. und die gewünschten ausgeben kann. :frowning:

Ich hatte mir zuerst gedacht jeder Telefonnummer eine eigene ID zugeben, aber das würde mir ja nichts bringen, da ich nicht zwischen den Nummer unterscheiden kann?!

Danke schon einmal im Voraus! :slight_smile:

LG
Benni

Du brauchst auf jeden Fall etwas um die Telefonnummer sortieren zu können. Eine serial Spalte könnte man dafür verwenden, man kann sich aber auch vorstellen, dass Du eine Spalte “sort_order” einführst, mit der man manuell die Reihenfolge der Nummern festlegen kann.

Testdaten:

create table person
(
   person_id integer not null primary key,
   firstname varchar(100) not null,
   lastname  varchar(100) not null
);

create table phone
(
  phone_number_id serial not null primary key,
  person_id    integer not null references person,
  phone_number varchar(20) not null
);

insert into person 
  (person_id,firstname,lastname) 
values 
  (1,'Arthur','Dent'), 
  (2,'Zaphod','Beeblebrox'),
  (3,'Tricia','McMillian');

insert into phone 
  (person_id, phone_number)
values
  (1, 'arthur-1'),
  (1, 'arthur-2'),
  (1, 'arthur-3'),
  (2, 'zaphod-1'),
  (2, 'zaphod-2'),
  (3, 'tricia-1'),
  (3, 'tricia-2');

Die folgende Abfrage:

with numbered_phones as (
    select phone_number_id, 
           person_id, 
           phone_number, 
           row_number() over (partition by person_id order by phone_number_id) as rn
    from phone 
)
select p.person_id, 
       p.firstname, 
       p.lastname, 
       pn.phone_number
from person p
  join numbered_phones pn on pn.person_id = p.person_id and pn.rn = 1
order by p.person_id;

liefert dann folgendes Ergebnis:

person_id | firstname | lastname   | phone_number
----------+-----------+------------+-------------
        1 | Arthur    | Dent       | arthur-1    
        2 | Zaphod    | Beeblebrox | zaphod-1    
        3 | Tricia    | McMillian  | tricia-1

Ich überlasse es Dir die Abfrage auf die 2 oder 3. Nummer abzuändern.

Vielen herzlichen Dank! :slight_smile:

Habe es nun geschafft…

könntest du mir bitte vielleicht noch verraten, wie das “row_number() over” und “partition by” wirkt, bzw. was es genau macht?
habe gerade die doku gelesen, werde daraus aber nicht wirklich schlau :frowning:

Das ist eine window-funktion. Sie zählt Gruppen von Zeilen. Die Gruppen unterteilt man via PARTITION BY, die Sortierung mit ORDER.

Stell Dir vor, du hast N Spieler, jeder hat eine Reihe von Versuchen gemacht. Du willst von jedem die 3 besten werten: partition by spieler order by resultat desc. Damit bekommst Du eine Auflistung, wo je Spieler seine Versuche nummeriert sind, der beste Versuch beginnend. In einem äußeren select schaust nach allen, wo diese Nummerierung kleiner gleich 3.

Es gibt noch ca. 300 Millionen weitere Anwendungen dafür …

Vielen Dank, habe es nun verstanden :slight_smile: