SQL-Abfrage

[Lothar Geyer]

Offenbar habe ich zu viele Ostereier gegessen - ich will eine einfache
Abfrage machen und kriege das nicht hin (Access und SQLserver).

In einer Tabelle sind alle Auftragspositionen enthalten. Ich will von
jedem Artikel die zwei Datensätze mit den größten Mengen.

Bitte helft mir von der Leitung, auf der ich stehe ...

Lothar Geyer

[Lutz Donnerhacke]

* Lothar Geyer wrote:
> In einer Tabelle sind alle Auftragspositionen enthalten. Ich will von
> jedem Artikel die zwei Datensätze mit den größten Mengen.

Hausaufgabe?

# CREATE TABLE positionen (
id SERIAL NOT NULL PRIMARY KEY,
name VARCHAR(40),
menge INT
);

# SELECT * from positionen;
id | name | menge
----+-------+-------
1 | test | 5
2 | test | 15
3 | test | 1
4 | test1 | 1
5 | test | 15
6 | test | 5


# SELECT name, menge AS top1,
(SELECT menge FROM positionen t2
WHERE t2.id = (SELECT max(id) FROM positionen
WHERE name = t1.name
AND id <> t1.id
AND menge = (SELECT max(menge)
FROM positionen
WHERE name = t1.name
AND id <> t1.id))) AS top2
FROM positionen t1
WHERE t1.id = (SELECT max(id) FROM positionen
WHERE name = t1.name
AND menge = (SELECT max(menge) FROM positionen
WHERE name = t1.name));
name | top1 | top2
-------+------+------
test1 | 1 |
test | 15 | 15

[Marc Santhoff]

Am Tue, 10 Apr 2012 15:21:03 +0000 schrieb Lutz Donnerhacke:


> # SELECT name, menge AS top1,
> (SELECT menge FROM positionen t2
> WHERE t2.id = (SELECT max(id) FROM positionen
> WHERE name = t1.name
> AND id <> t1.id
> AND menge = (SELECT max(menge)
> FROM positionen
> WHERE name = t1.name
> AND id <> t1.id))) AS top2
> FROM positionen t1
> WHERE t1.id = (SELECT max(id) FROM positionen
> WHERE name = t1.name
> AND menge = (SELECT max(menge) FROM positionen
> WHERE name = t1.name));
> name | top1 | top2
> -------+------+------
> test1 | 1 |
> test | 15 | 15

Kannst Du bitte kurz erläutern, wie und warum Du jedesmal noch ein
"SELECT max(id) ..." dazwischen gesetzt hast?

Die Grundstruktur verstehe ich, erst die Menge mit den Top-Werten
auswählen, und zusammenpappen mit der Menge der Top2-Werte, die die erste
Menge ausgrenzt und die verbleibenden Maxima sammelt. Aber auf den
Kunstgriff mit max(id) wäre ich nicht gekommen ...

Marc

[Lutz Donnerhacke]

* Marc Santhoff wrote:
> Kannst Du bitte kurz erläutern, wie und warum Du jedesmal noch ein
> "SELECT max(id) ..." dazwischen gesetzt hast?

Deiner Aufgabenstellung fehlte das Constraint UNIQUE(name, menge).

[Lutz Donnerhacke]

In de.comp.datenbanken.misc, you wrote:
> # SELECT name, menge AS top1,
> (SELECT menge FROM positionen t2
> WHERE t2.id = (SELECT max(id) FROM positionen
> WHERE name = t1.name
> AND id <> t1.id
> AND menge = (SELECT max(menge)
> FROM positionen
> WHERE name = t1.name
> AND id <> t1.id))) AS top2
> FROM positionen t1
> WHERE t1.id = (SELECT max(id) FROM positionen
> WHERE name = t1.name
> AND menge = (SELECT max(menge) FROM positionen
> WHERE name = t1.name));

Da nur die Mengenangabe, aber nicht die Position des TOP2 Wertes
interessiert ist es möglich, deutlich zu verkürzen.

# SELECT name, menge AS top1,

(SELECT max(menge) FROM positionen
WHERE name = t1.name AND id <> t1.id) AS top2

[Marc Santhoff]

Ich bin nicht der OP, war aber fasziniert von deiner Lösung - man lernt
ja gern dazu.

Zusammen mit dem anderen Posting verstehe ich's natürlich.

Danke,
Marc

[Dieter Nöth]

Lothar Geyer wrote:

> Offenbar habe ich zu viele Ostereier gegessen - ich will eine einfache
> Abfrage machen und kriege das nicht hin (Access und SQLserver).
>
> In einer Tabelle sind alle Auftragspositionen enthalten. Ich will von
> jedem Artikel die zwei Datensätze mit den größten Mengen.

Zumindest in SQL Server ist das ganz einfach (ob Access inzwischen
OLAP-Funktionen kann, weiss ich nicht):

select *
from
(select t.*,
row_number() over (partition by artikel order by menge desc) as rn
) as dt
where rn <= 2

Je nachdem, was du genau willst (genau zwei Rows/alle Rows mit den
beiden höchsten Mengen/...), kannst du auch RANK oder DENSE_RANK nehmen.

Dieter

[Lothar Geyer]

Hallo Lutz,

Danke für Deine Mühe. Deinen Lösungsansatz hatte ich auch schon
angedacht, aber er war mir eigentlich zu kompliziert. Deshalb hatte ich
etwas mit Top 2 und Group By versucht, bin aber zu keinem Ergebnis
gekommen. Also werde ich mir Deine Lösung "zu eigen" machen. Nochmals
vielen Dank.

Lothar Geyer

PS: Ich habe gerade noch ein anderes Problem, das ich in einem neuen
Thread posten werde. Darf ich dort auch auf Deine Hilfe hoffen? Ich
"kann" zwar SQL, aber sicher nicht in allen Feinheiten - und habe auch
nicht die laufende Übung.

[Lothar Geyer]

Hallo Dieter,

Danke für Deine Lösung. Da die Abfrage auf Access und SQLserver laufen
soll, versuche ich, spezielle Eigenheiten zu vermeiden - was sicher
nicht immer zu den optimalen Lösungen führt, dafür aber die Wartbarkeit
erhöht.
(Natürlich kann man jetzt diskutieren, dass die Performance sich laufend
- bei jeder Abfrage - auswirkt, zwei Statements aber nur im Fehler- bzw.
Problemfall zu bearbeiten sind.)

Lothar Geyer