Top X mit Group by

[batzenlurch]

Hallo Zusammen,

Ich stehe gerade auf'm Schlauch und Google kann mir trotz zahlloser Einträge trotzdem gerade nicht helfen. Ich hoffe auf O.Helper ;-)

Zum Thema. Ich möchte aus einer Tabelle die letzten 3 Aufträge zu einem Artikel herausfinden und bekomm den SQL String nicht hin:
Tabelle KA1:
AuftragsNr ArtikelNr Datum
11.2 1 20090112
22.1 1 20090112
31.3 1 20090110
12.a 2 20090114
43 4 20090522
34.1 1 20090101

Mein bisheriger Code:

SELECT TOP 3 KA1.AuftragsNr, KA1.ArtikelNr FROM 
(
  SELECT AuftragsNr, ArtikelNr, Datum
  FROM KA1
  ORDER BY DATUM DESC
) AS tmp_KA1 INNER JOIN KA1 ON (KA1.AuftragsNr = tmp_KA1.AuftragsNr) AND (KA1.ArtikelNr = tmp_KA1.ArtikelNr)
GROUP BY KA1.ArtikelNr, KA1.AuftragsNr
ORDER BY KA1.ArtikelNr

Bitte um eine kleine Hilfestellung.
Danke!

Gruß

[O. Helper]

Hallo Batzenlurch,

die letzten 3 Aufträge zu einem Artikel

Wirklich nur zu einem bestimmten Artikel?

[highlight=SQL]
SELECT TOP 3 AuftragsNr, ArtikelNr, Datum
FROM KA1
WHERE ArtikelNr = '1'
ORDER BY DATUM DESC, AuftragsNr
[/highlight]

TOP arbeitet etwas anders in Access, als man es von anderen kennt & erwartet.
Es hört nicht wirklich nach 3 Datensätze auf, weil Acces nicht entscheidet, welche Datensätze rausfallen sollen, wenn es der Sortierung nach nicht eindeutig ist.
Wenn Du z.B. am jüngsten Tag 5 Aufträge hast, bekommst Du die 5 zurückgeliefert und nicht 3.
Deshalb immer noch nach weiteren Felder sortieren => Auftragsnummern

[batzenlurch]

Hallo O.Helper,

erst mal recht herzlichen Dank für deine Schnelle Antwort.
Ich habe mich ein wenig missverständlich ausgedrückt - ginge es nur um einen Artikel, wäre die Abfrage wirklich sehr leicht. Es geht allerdings um ALLE Artikel; d.h. ich möchte nachher zu jedem Artikel die letzten 3 Aufträge in einer Liste sehen.

Solch eine Abfrage habe ich schon in Oracle usw. gemacht, aber in Access will es mir nicht gelingen. Hatte auch schon an eine Schleife in VBA gedacht... die Performance wäre allerdings nicht zufriedenstellend bei ca. 30k Artikeln :-(

Hast Du evtl. noch eine Idee?
Danke & Grüßle

[O. Helper]

geht allerdings um ALLE Artikel

Habe ich mir gedacht , wäre ja wirklich zu einfach, aber meistens hält man sich an das, was die Leute schreiben.

Dein erster Ansatz sollte nirgend gehen, da a) eine Sortierung in einen Inside-View nicht erlaubt ist und b) das TOP liefert Dir die ersten 3 Datensätze, aber nicht je Gruppe.

Eine einfach Lösung ist, Du zählst jedesmal, wieviele "Vorgänger" es gab, alles ab 3en werden rausgefiltert. Ich habe es mal als MSSQL Script gemacht, das ist für mich bequem als mit Access.
[highlight=SQL]CREATE TABLE #KA1(AuftragsNr varchar(4), ArtikelNr int, Datum datetime);

INSERT INTO #KA1 VALUES('11.2', 1, '20090112');
INSERT INTO #KA1 VALUES('22.1', 1, '20090112');
INSERT INTO #KA1 VALUES('31.3', 1, '20090110');
INSERT INTO #KA1 VALUES('31.x', 1, '20090110'); -- Problem
INSERT INTO #KA1 VALUES('12.a', 2, '20090114');
INSERT INTO #KA1 VALUES('43', 4, '20090522');
INSERT INTO #KA1 VALUES('34.1', 1, '20090101');

SELECT AuftragsNr, ArtikelNr, Datum
FROM #KA1
WHERE (SELECT COUNT(*)
FROM #KA1 AS Sub
WHERE SUB.ArtikelNr = #KA1.ArtikelNr
AND SUB.Datum >= #KA1.Datum) <= 3
ORDER BY ArtikelNr, Datum DESC, AuftragsNr

GO
DROP TABLE #KA1[/highlight]
[highlight=code]AuftragsNr ArtikelNr Datum
---------- ----------- -----------------------
11.2 1 2009-01-12 00:00:00.000
22.1 1 2009-01-12 00:00:00.000
12.a 2 2009-01-14 00:00:00.000
43 4 2009-05-22 00:00:00.000[/highlight]
Das SELECT funktioniert auch unter Access (habs ausprobiert, nur das # bei #KA1 entfernen).

Dann habe ich noch ein "Problemfall" eingebaut, ist das gleiche, was ich schon zu TOP schrieb.
Wenn es also die Grenze 3 mitten in einen Tagen mit mehreren Aufträgen fällt.

Da müsste man noch die WHERE Klausel bei der Vorgänger-Ermittlung ändern ... aber etwas sollst Du auch zu tun haben , allein schon, damit Du Dich zukünftig auf Dich selbst verlässt.

[batzenlurch]

Die Datenbank kann einen solchen Subquery leider nicht ausführen :-(
Ich habe den Query mal ein wenig umgemodelt:

SELECT KA1.AuftragsNr, KA1.ArtikelNr, KA1.Datum, tmp_KA.ArtikelNr
FROM  (SELECT COUNT(*) AS Anzahl, Sub.ArtikelNr
       FROM KA1 AS Sub
       GROUP BY Sub.ArtikelNr
       ) AS tmp_KA INNER JOIN KA1 ON (tmp_KA.ArtikelNr=KA1.ArtikelNr)
WHERE tmp_KA.Anzahl <= 5
ORDER BY KA1.ArtikelNr, KA1.Datum DESC, KA1.AuftragsNr

Sieht auf den ersten Blick ganz gut aus. Problem: Alle Artikel die >5 verkauft/produziert wurden werden nicht ausgegeben...

Evtl. einen anderen Ansatz ohne direkten Subquery?

Gruß

[O. Helper]

Was heisst nicht ausführen, Fehlermeldung oder läuft es extrem lange (für jeden DS die Vorgänger zu zählen, ist nicht performant).

Von SQL her läuft es bei mir in Access aber; Access klammert es mir sogar wie üblich in reichlichem Masse:
[highlight=SQL]SELECT KA1.AuftragNr, KA1.ArtikelNr, KA1.Datum
FROM KA1
WHERE ((((SELECT COUNT(*)
FROM KA1 Sub
WHERE SUB.ArtikelNr = KA1.ArtikelNr
AND SUB.Datum >= KA1.Datum))<=3))
ORDER BY KA1.ArtikelNr, KA1.Datum DESC , KA1.AuftragNr;[/highlight]

[batzenlurch]

Es erscheint die Fehlermeldung:

ODBC-Aufruf Fehlgeschlagen
[Marxmeier][SQL/R ODBC Server]IM001 - unable to obtain message #100:97
Subqueries not supported near "SELECT" (#-1)

...

Gruß

[O. Helper]

Das ist keine Access Fehlermeldung, sonder eine von ODBC bzw. der dahinterliegenden Datenquelle; KA1 ist also eine per ODBC verknüpfte Tabelle?

Sieh mal, wenn das Statement halbwegs nach Ansi-SQL aussieht, dann sendet Access 1:1 an die Datenquelle weiter. Da kannst Du (Wir) Dir Gedanken machen, was Access unterstützt, soviel wir lustig sind; das spielt hier keine Rolle. Wichtig ist, was der ODBC Treiber und die dahinterliegende DBMS unterstützen.

Es gibt da einen leicht schmutzigen Trick, der vielleicht bei Dir klappen kann.
Nimm mein letztes Statement und füge mal ein CStr ein, ungefähr so:

SELECT CStr(KA1.AuftragNr) AS AuftrNr, ....

CStr ist VBA Kram, da weiß Access, das das kein anderer kann.
Es werden deshalb alle Daten ungefiltert aus der Datenquelle geladen und Access verarbeitet sie dann weiter; das kann das Statement.
Bei vielen Daten ist das natürlich ein echtes Problem, da ziehst Du den Server dann auch mit an den Grund.

[batzenlurch]

Die schmutzige Lösung funktioniert leider auch nicht.
Mir gehen auch die Ideen aus.

ps. Sorry für das verspätete Feedback.

[O. Helper]

Schade. Ich musste mal einen ODBC Treiber verwenden, der auch nicht alles unterstützt hatte; da hatte es damit gut funktioniert. Versuche es auch mal an einer anderen Stelle, z.B. im ORDER BY mit dem Datum; das könnte vielleicht eher klappen.

Ansonsten gibt es immer (meistens) für ein Problem mehrere Lösungen; versuchen wir mal die nächste. Allerdings habe ich gerade kein Access zum Testen zur Hand.
Das nutzt "nur einen normalen" JOIN, ich fürchte aber, das Access das COUNT(SUB.AuftragsNr) nicht wirklich hin bekommt.
Dafür würde mit dem Statement der "Problemfall" mit abgedeckt.

[highlight=SQL]CREATE TABLE #KA1
(AuftragsNr varchar(4), ArtikelNr int, Datum datetime);

INSERT INTO #KA1 VALUES('11.2', 1, '20090112');
INSERT INTO #KA1 VALUES('22.1', 1, '20090112');
INSERT INTO #KA1 VALUES('31.3', 1, '20090110');
INSERT INTO #KA1 VALUES('31.x', 1, '20090110'); -- Problem
INSERT INTO #KA1 VALUES('12.a', 2, '20090114');
INSERT INTO #KA1 VALUES('43', 4, '20090522');
INSERT INTO #KA1 VALUES('34.1', 1, '20090101');

SELECT MAIN.AuftragsNr, MAIN.ArtikelNr, MAIN.Datum,
COUNT(SUB.AuftragsNr)
FROM #KA1 AS MAIN
LEFT JOIN #KA1 AS SUB
ON MAIN.ArtikelNr = SUB.ArtikelNr
AND (
(MAIN.Datum = SUB.Datum
AND MAIN.AuftragsNr > SUB.AuftragsNr
) OR
(MAIN.Datum < SUB.Datum)
)
GROUP BY MAIN.ArtikelNr, MAIN.Datum, MAIN.AuftragsNr
HAVING COUNT(SUB.AuftragsNr) < 3
ORDER BY MAIN.ArtikelNr, MAIN.Datum DESC, MAIN.AuftragsNr
GO
DROP TABLE #KA1[/highlight]

Ergebnis wie zuletzt, nur besser

[batzenlurch]

Danke! Es funktioniert.
Die Abfrage braucht zwar 10min aber damit kann ich erst einmal leben.
Versuche noch ein paar Einschränkungen rein zu bekommen, damit die Abfrage schneller wird.

Zur Info:
Es werden 160k Einträge durchsucht.

Noch mal herzlichen Dank!

[O. Helper]

Nun, die Aufträge werden fest nur die ArtikelNr gejoin, sonst nur noch über "größer Datum", entsprechend werden viele Produkte gebildet, die erst hinterher größtenteils (bis auf die ersten 3) wieder verworfen werden.
Das ist schon verständlich, das es nicht sehr performant ist.

[batzenlurch]

Noch eine Frage:

Deine Abfrage:

SELECT MAIN.AuftragsNr, MAIN.ArtikelNr, MAIN.Datum,
COUNT(SUB.AuftragsNr)
FROM #KA1 AS MAIN
LEFT JOIN #KA1 AS SUB
ON MAIN.ArtikelNr = SUB.ArtikelNr
AND (
(MAIN.Datum = SUB.Datum
AND MAIN.AuftragsNr > SUB.AuftragsNr
) OR
(MAIN.Datum < SUB.Datum)
)
GROUP BY MAIN.ArtikelNr, MAIN.Datum, MAIN.AuftragsNr
HAVING COUNT(SUB.AuftragsNr) < 3
ORDER BY MAIN.ArtikelNr, MAIN.Datum DESC, MAIN.AuftragsNr

SELECT MAIN.AuftragsNr, MAIN.ArtikelNr, MAIN.Datum,
       COUNT(SUB.AuftragsNr)
FROM #KA1 AS MAIN
     LEFT JOIN #KA1 AS SUB
         ON MAIN.ArtikelNr = SUB.ArtikelNr
            AND (
                 (MAIN.Datum = SUB.Datum
                  AND MAIN.AuftragsNr > SUB.AuftragsNr
                 ) OR
                 (MAIN.Datum < SUB.Datum)
                )
WHERE Left([MAIN.DATUM],4)>Year(Now())-4
GROUP BY MAIN.ArtikelNr, MAIN.Datum, MAIN.AuftragsNr
HAVING COUNT(SUB.AuftragsNr) < 3
ORDER BY MAIN.ArtikelNr, MAIN.Datum DESC, MAIN.AuftragsNr

Wenn ich den Select noch um eine Where Klausel erweiter, wird der erste Wert in der DB (d.h. der letzte bestellte Auftrag zum Artikel) nicht angezeigt.

Damit das ganze ein wenig performanter ist würde ich das Jahr noch gerne einschrenken...

Gruß,
Batze

[O. Helper]

Ich habe Deine Daten nicht, deshalb kann ich des nicht nachvollziehen; bei den Handvoll Demo-Daten ging es über den Datumsfilter.

Left([MAIN.DATUM],4)>Year(Now())-4

Wie war das noch, links ist da wo der Daumen rechts ist?
[highlight=code]?"Ist " & Left(now() ,4) & " > " & Year(Now())-4 & "? " & (Left(now() ,4) > Year(Now())-4)
Ist 27.0 > 2005? Wahr[/highlight]

Und wenn Du mit

WHERE YEAR( [MAIN.DATUM] ) > Year(Now()) - 4

filterst?

[batzenlurch]

Das DB Feld ist als Text hinterlegt, deswegen muss ich auf die "Left-Lösung" zurück greifen. Komischerweise bringt er die richtigen Werte, wenn ich bei der Where Klausel auch noch die Artikelnummer einschränke.

WHERE Left([MAIN.S114],4)>Year(Now())-4 AND MAIN.ArtikelNr BETWEEN "10050" AND "12000"

Wenn Dir auf die schnelle dazu etwas einfällt wär es ganz gut, ansonsten lasse ich die Einschränkung einfach weg und der Select läuft halt 2min länger...

Also noch mal vielen Dank!