SQL fuer max()

Liebe Gemeinde,

ich habe folgende 2 Tabellen:

table USERS:
userid    groupid
CREON     SYSMGR
CV        SYSMGR
DRÖGE     SYSMGR
EOBJJ     LL
EQCVG     SYSMGR
EQFCC     SYSMGR
EQLDI     LL
ERMCC     SYSMGR
EVAUS     SYSMGR
EVMOI     LL
GCQBD     LL



table GROUPPROGRAM:
groupid	capability
BACKGR    0
SYSMGR    3
SYSMGR    0
ENTSCH    0
SYSMGR    3
LL        3

Ich möchte einen SQL finden, der folgendes Ergebnis zurückgibt:

userid, groupid, max(capability) pro groupid
CREON	SYSMGR	3
CV	   SYSMGR	3
DRÖGE	SYSMGR	3
EOBJJ	LL	    3
EQCVG	SYSMGR	3
EQFCC	SYSMGR	3
EQLDI	LL	    3
ERMCC	SYSMGR	3
EVAUS	SYSMGR	3
EVMOI	LL	    3
GCQBD	LL	    3

Es soll also die Tabelle USER zurückgegeben werden, mit einer zusätzlichen Spalte, die die max(capability) pro GROUPID für jede USERID enthält. Die max(capability) ist hier zufällig immer 3, richtet sich aber natürlich nach dem höchten Wert aus der Tabelle GROUPPROGRAM pro GROUPID

Kann mir jemand weiterhelfen?

Vielen Dank

test=*# select users.*,max(GROUPPROGRAM.capability) from users left join GROUPPROGRAM on users.groupid=GROUPPROGRAM.groupid group by users.userid,users.groupid;
 userid | groupid | max
--------+---------+-----
 CV     | SYSMGR  |   3
 CREON  | SYSMGR  |   3
 EQLDI  | LL      |   3
 EQCVG  | SYSMGR  |   3
 ERMCC  | SYSMGR  |   3
 DROEGE | SYSMGR  |   3
 GCQBD  | LL      |   3
 EVAUS  | SYSMGR  |   3
 EQFCC  | SYSMGR  |   3
 EVMOI  | LL      |   3
 EOBJJ  | LL      |   3
(11 rows)

Hallo akretschmer,

vielen Dank für die Antwort. !!! Hatte vor lauter Weihnachtsstress gar nicht deine Antwort bemerkt und vergessen die Weiterleitung per email einzuschalten.

Es funktioniert. Aber nur wenn keine anderen Felder aus joins in dem Zusammenhang mit dem hier verwendeten “GROUP BY” dabei sind. Sprich folgender SQL gibt den Fehler zurück:

ORA-00979: Kein GROUP BY-Ausdruck
00979. 00000 - “not a GROUP BY expression”
*Cause:
*Action:
Fehler in Zeile: 4 Spalte: 2


Hier der SQL der diesen Fehler produziert

SELECT   
 u.userid,
 u.groupid,
 e.employeename,
 e.deletion,
 e.telex,
 e.depid,
 e.labgrpid,
 e.place,
 e.buildingroom,
 g.labcostcenter,
 g.LOCATION,
 g.descriptionlabgroup,
 max(c.capability)
FROM 
 users u,
 employee e,
 labgroup g,
 groupprogram c
WHERE (
 (u.userid = e.empid(+)) AND
 (e.labgrpid = g.labgrpid(+)) AND
 (u.groupid = c.groupid(+)) AND
 ((u.flactive = 'Y') or (trim(u.flactive) is NULL)) AND
 (e.deletion = 'N')
)
GROUP BY u.userid, u.GROUPID;

Lösche ich die Felder, die nicht in den beiden “GROUP BY” Tabellen stehen, bekomme ich Werte zurück:

SELECT   
 u.userid,
 u.groupid
 max(c.capability)
FROM 
 users u,
 employee e,
 labgroup g,
 groupprogram c
WHERE (
 (u.userid = e.empid(+)) AND
 (e.labgrpid = g.labgrpid(+)) AND
 (u.groupid = c.groupid(+)) AND
 ((u.flactive = 'Y') or (trim(u.flactive) is NULL)) AND
 (e.deletion = 'N')
)
GROUP BY u.userid, u.GROUPID;

Wie bekomme ich die Anzeige der Werte der Felder aus den Tabellen e (employee) und g (labgroup) mit in die Anzeige?

Mit freundlichem Gruß

Alle Spalten des Resultates müssen entweder aggregiert oder gruppiert sein. Das ist eine eiserne Regel. Was Du machen kannst ist wieder über die funktionierende Abfrage die ‘große’ Tabelle dazujoinen. (über die 3 Spalten)

Anderer Ansatz wäre die Verwendung von Window-Funktionen.

Hallo akretschmer,

danke für die schnelle Antwort.
Ich weis leider nicht wie ich die ‘grosse tabelle’ über die 3 Spalten (es ist wohl u.userid, u.groupid, max(c.capability gemeint) joinen kann.

Kannnst du mir das schematisch für mein Beispiel aufzeigen?

Gruß

select * from big_table left join (hier deine andere abfrage) foo on (bigtable.c1,bigtable.c2,bigtable.c3) = (foo.c1,foo.c2.foo.c3)