[MSSQL] Problemen met GROUP BY

[Thralas]

Situatieschets
Ik heb een tabel met daarin gegevens van gebruikers. Iedere gebruiker heeft één username, en mogelijk per username meerdere aliassen, welke ieder weer een bepaalde rank hebben. Aan elke row hang ook een id, welke als het goed is unique is (column is echter niet defined als unique, database is niet door mij ontworpen ).

Id	Username	Alias	Rank
1	Foo	FooA	21
2	Foo	FooB	87
3	Bar	BarA	21
5	Bar	BarB	45
6	Bar	BarC	45

Nu wil ik uit deze tabel een recordset met maar één row per username, en dan wel de row met de hoogste rank. In de fictieve tabel hieronder dus de groengekleurde rows. Rank is echter niet uniek per username en er kunnen per username dus meerdere rows zijn met dezelfde rank (zie row met id 5 en 6). In dit geval pak ik de row met het laagste id.

Het probleem
Een recordset per username returnen is het probleem niet, dat lukt met een GROUP BY username. Vervolgens kan ik óók de hoogste rank erbij halen door MAX(rank) te selecten.

SQL:

SELECT username, MAX(rank) AS rank FROM data GROUP BY username

Het probleem is echter dat ik dus óók de bijbehorende alias wil weten, bij bovenstaande query zou ik bijvoorbeeld Foo 87 en Bar 45 als resultaat krijgen, terwijl ik ook de bijbehorende aliassen wil weten (FooB en BarB). Alias toevoegen mag van MSSQL niet omdat dit veld niet voorkomt in de GROUP BY. _{En nu valt me op dat ik dan alsnog een probleem heb in het geval van een rank die meerdere malen voorkomt per user, zoals in het voorbeeld. Iets zegt me dat de hele aanpak met een GROUP BY niet klopt.}

Van iemand anders kreeg ik de suggestie om het als volgt te proberen:

SQL:

SELECT a.username, MAX(a.rank) AS rank, b.alias FROM data a INNER JOIN data b ON a.username = b.username GROUP BY a.username

Alleen dan krijg ik als nog:

code:

Server: Msg 8120, Level 16, State 1, Line 1
Column 'b.alias' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Deze toevoegen aan de GROUP BY zorg weer dat ik meerdere rows per username krijg. Dit doet mij dan ook twijfelen aan de hele aanpak met een GROUP BY...

[Swerfer]

Ik ben niet bekend met groupe by, maar als je max(alias) as alias toevoegt, dan werkt het zaakje wel...

Is niet een mooie oplossing... Er zal vast wel iemand met een charmantere oplossing komen

edit:

Je kan ook group by username,alias toepassen, of zit ik dan helemaal verkeerd?

[ Voor 21% gewijzigd door Swerfer op 07-05-2006 14:58 ]

[NMe]

Hoe had je verwacht dat je die aliassen terugkrijgt dan? Elk in een eigen kolom in je recordset? Dat kan dus niet he? Als je alle aliassen terug wil krijgen, dan zul je toch echt meerdere records terug gaan krijgen. En als je maar één alias wil, dan zul je er een aggregate functie overheen moeten gooien. Lees er anders ook onze SQL-FAQ nog eens op na, daar staat ditzelfde ook uitgelegd.

[kenneth]

Swerfer schreef op zondag 07 mei 2006 @ 14:53:
Ik ben niet bekend met groupe by, maar als je max(alias) as alias toevoegt, dan werkt het zaakje wel...

Is niet een mooie oplossing... Er zal vast wel iemand met een charmantere oplossing komen

Het is niet alleen niet charmant, het is zelfs fout
Met MAX(alias) krijg je de maximale (eerste in descending order) alias los van de rank.

[Thralas]

-NMe- schreef op zondag 07 mei 2006 @ 15:07:
Hoe had je verwacht dat je die aliassen terugkrijgt dan? Elk in een eigen kolom in je recordset? Dat kan dus niet he? Als je alle aliassen terug wil krijgen, dan zul je toch echt meerdere records terug gaan krijgen. En als je maar één alias wil, dan zul je er een aggregate functie overheen moeten gooien. Lees er anders ook onze SQL-FAQ nog eens op na, daar staat ditzelfde ook uitgelegd.

Ik wil natuurlijk niet alle aliassen, maar alleen degene die bij de username en rank 'hoort'. Een aggregrate erop loslaten kan dus niet, aangezien je met MAX() helemaal fout zit. Daarom ben ik, denk ik, ook fout bezig met GROUP BY en was het hele idee van mij om GROUP BY gebaseerd op een verkeerd idee van de werking van GROUP BY. Ik heb alleen dus geen flauw idee hoe het dan wel zou lukken.

[Verwijderd]

wat dacht je van

SQL:

select * 
from data data1
where alias in(
    select max(alias)
    from data data2
    where data1.Username=data2.Username
)

[Thralas]

Nee dat is het helaas ook niet. MAX(alias) returned sowieso de alfabetisch-laatste alias...

[Verwijderd]

Uitgaand van de groene balkjes in je voorbeeld, kom ik op iets als:

SQL:

select 
  a.Username,
  max(a.Rank) Rank,
  (select top 1 Alias from data
   where Username = a.Username
   and Rank = (select max(Rank) from data
               where Username = a.Username)
   order by Id) Alias
from data a
group by a.Username

Van de dubbele Aliases wordt dan alleen die met de laagste Id teruggegeven, maar dat is in je voorbeeld ook zo. Overigens niet kunnen testen, want ik heb nu geen MSSQL bij de hand...

[Thralas]

En die werkt. Zat ik toch in de buurt met een group by.

Bedankt allemaal.