[T-SQL] Problemen met query met een GROUP BY voor een view*

[Slagroom]

Hallo,

Ik ben al een geruime tijd bezig met MySQL maar nu ben ik in MS SQL (SQL Server 2005 Express) aan het spelen.

Ik heb de volgende query voor een view uitgedacht:

SQL:

SELECT     dbo.Horse.Id, dbo.HorseVersion.Modified, dbo.HorseVersion.Number, dbo.HorseVersion.Name, dbo.HorseSex.Name AS HorseSexName, 
                      dbo.HorseVersion.Breeding, dbo.HorseVersion.StudbookNumber, dbo.HorseVersion.StudbookName, dbo.HorseVersion.BirthDate, 
                      dbo.HorseVersion.ChipNumber, dbo.HorseType.Name AS HorseTypeName, dbo.HorseVersion.OwnersShareCosts, dbo.Owner.Name AS OwnerName, 
                      dbo.Location.Name AS LocationName, dbo.HorseVersion.StablingCosts, dbo.HorseVersion.StablingVatRate, dbo.HorseVersion.Comments
FROM         dbo.Horse INNER JOIN
                      dbo.HorseType ON dbo.Horse.Id = dbo.HorseType.Id INNER JOIN
                      dbo.HorseVersion ON dbo.Horse.Id = dbo.HorseVersion.HorseId INNER JOIN
                      dbo.HorseSex ON dbo.HorseVersion.HorseSexId = dbo.HorseSex.Id INNER JOIN
                      dbo.Location ON dbo.HorseVersion.LocationId = dbo.Location.Id INNER JOIN
                      dbo.Owner ON dbo.HorseVersion.InvoiceOwnerId = dbo.Owner.Id
GROUP BY dbo.HorseVersion.HorseId

Hij moet alleen de horses laten zien met een unieke id. Er is namelijk de tabel Horse en de tabel HorseVersion. Iedere wijziging wordt in de tabel HorseVersion opgeslagen zodat het complete wijzigingstraject gevolgd kan worden.

Hij geeft echter een foutmelding bij het uitvoeren van deze query waar ik weinig van snap. Namelijk:

code:

Msg 8120, Level 16, State 1, Line 1
De kolom dbo.Horse.Id is ongeldig in de selectielijst omdat deze niet is opgenomen in een statistische functie of de GROUP BY-component.

Nu zou ik dbo.Horse.Id in mijn GROUP BY component op kunnen nemen maar dan zegt hij dat hij de volgende kolom mist. En als ik alle kolommen toevoeg heeft de GROUP BY geen zin meer...

Misschien ben ik helemaal verkeerd bezig op heb ik een volledig verkeerde insteek maar ik sta open voor kritiek. Graag zelfs!

Wie kan en wil mij helpen?

[Cyphax]

Die foutmelding die je krijgt betekent dat je met je group by geen unieke rijen terugkrijgt.
Ergens in jouw query probeert je DB een aantal rijen te groeperen, maar in een van je velden is bij die rijen de waarde anders. Welke moet ie dan pakken? Op dat moment krijg je die melding van je DB terug. Bekijk eerst de output van je query eens zonder group by, en ga dan na welk(e) veld(en) ervoor zorgen dat jouw query niet meer gegroepeerd kan worden. Kijk naar velden die in rijen die jij wilt groeperen afwijken ten opzichte van elkaar.

[ Voor 36% gewijzigd door Cyphax op 24-10-2006 11:11 ]

[whoami]

Normaal gezien ga je ieder veld dat in je select list komt, en geen aggregate method is, in je group by method zetten.
Bv:

code:

select naam, voornaam, max(datum) 
from tabel
group by naam, voornaam

Wat jij doet is een ranzigheid die werkt in MySQL, maar in geen enkele andere (afaik) dbms werkt.

Verder zie ik zelfs geen aggregate method in je select, dus heeft die group by in deze query geen nut.
Zie ook:
klik

En als ik alle kolommen toevoeg heeft de GROUP BY geen zin meer

Idd, want je hebt geen aggregate functie[/]

Ik denk dat je het eerder zo zult moeten oplossen:

code:

select horse.id, horse_version.name
from horse
inner join horse_version on ....
where  horse.id = 1
and horse_version.version = ( select max(horse_version.version) from horse_version x
                                                 where x.horse_id = horse.id )

[ Voor 31% gewijzigd door whoami op 24-10-2006 11:16 ]

[Slagroom]

Aha! Subselects! Eens kijken of ik dat ook snap:

code:

SELECT     dbo.Horse.Id, dbo.HorseVersion.Modified, dbo.HorseVersion.Number, dbo.HorseVersion.Name, dbo.HorseSex.Name AS HorseSexName, 
                      dbo.HorseVersion.Breeding, dbo.HorseVersion.StudbookNumber, dbo.HorseVersion.StudbookName, dbo.HorseVersion.BirthDate, 
                      dbo.HorseVersion.ChipNumber, dbo.HorseType.Name AS HorseTypeName, dbo.HorseVersion.OwnersShareCosts, dbo.Owner.Name AS OwnerName, 
                      dbo.Location.Name AS LocationName, dbo.HorseVersion.StablingCosts, dbo.HorseVersion.StablingVatRate, dbo.HorseVersion.Comments
FROM         dbo.Horse INNER JOIN
                      dbo.HorseType ON dbo.Horse.Id = dbo.HorseType.Id INNER JOIN
                      dbo.HorseVersion ON dbo.Horse.Id = dbo.HorseVersion.HorseId INNER JOIN
                      dbo.HorseSex ON dbo.HorseVersion.HorseSexId = dbo.HorseSex.Id INNER JOIN
                      dbo.Location ON dbo.HorseVersion.LocationId = dbo.Location.Id INNER JOIN
                      dbo.Owner ON dbo.HorseVersion.InvoiceOwnerId = dbo.Owner.Id
WHERE     (dbo.Horse.Id, dbo.HorseVersion.Modified) IN
                          (SELECT     HorseId, max(dbo.HorseVersion.Modified)
                            FROM          dbo.HorseVersion HorseVersionSub
                            WHERE      HorseVersionSub.HorseId = dbo.Horse.Id)

Nee dus...

code:

Msg 102, Level 15, State 1, Line 11
De syntaxis bij , is onjuist.

Dit had ik gevonden op het internet:

code:

(dbo.Horse.Id, dbo.HorseVersion.Modified) IN

Maar lijkt een fout op te leveren. Zit ik er nu weer naast? Of maakt het nog iets uit dat dbo.HorseVersion.Modified een datetime veld is?

[whoami]

Eh,
Je kan niet testen op 2 velden in een IN clause.

code:

WHERE veld IN ( select blaat from tabel)

dat lukt, maar wat jij wilt doen is uit de lucht gegrepen.

[Janoz]

Die horseId kun je toch gewoon weglaten? In de where van je subselect geef je immers al aan dat je enkel die horse id wilt.

[Slagroom]

Misschien dat het niet klopt maar ik heb het hier gevonden:
http://www.faqs.org/docs/ppbook/x7300.htm

Andere dbms?

[Slagroom]

Janoz schreef op dinsdag 24 oktober 2006 @ 11:30:
Die horseId kun je toch gewoon weglaten? In de where van je subselect geef je immers al aan dat je enkel die horse id wilt.

Maar wat als er twee horses zijn met de zelfde Modified?

Is het misschien verstandiger (makkelijker is het wel denk ik) om de verschillende versies ook een Id te geven?

[ Voor 16% gewijzigd door Slagroom op 24-10-2006 11:34 ]

[justmental]

whoami schreef op dinsdag 24 oktober 2006 @ 11:29:
Eh,
Je kan niet testen op 2 velden in een IN clause.

code:

WHERE veld IN ( select blaat from tabel)

dat lukt, maar wat jij wilt doen is uit de lucht gegrepen.

Is dat echt zo
Met oracle gaat dit wel op deze manier.

[rrrandy]

Slagroom schreef op dinsdag 24 oktober 2006 @ 11:32:
[...]

Maar wat als er twee horses zijn met de zelfde Modified?

Is het misschien verstandiger (makkelijker is het wel denk ik) om de verschillende versies ook een Id te geven?

Dat heb je toch al ondervangen door de where in je subquery?

[Slagroom]

Maar de select in de subquery geeft maar één Modified terug... Modified is niet uniek. Dat zijn HorseId en Modified samen.

Maar misschien is dat een verkeerde aanpak.

Ik heb de versies een Id gegeven. De query is nu:

SQL:

SELECT     dbo.Horse.Id, dbo.HorseVersion.Modified, dbo.HorseVersion.Number, dbo.HorseVersion.Name, dbo.HorseSex.Name AS HorseSexName, 
                      dbo.HorseVersion.Breeding, dbo.HorseVersion.StudbookNumber, dbo.HorseVersion.StudbookName, dbo.HorseVersion.BirthDate, 
                      dbo.HorseVersion.ChipNumber, dbo.HorseType.Name AS HorseTypeName, dbo.HorseVersion.OwnersShareCosts, dbo.Owner.Name AS OwnerName, 
                      dbo.Location.Name AS LocationName, dbo.HorseVersion.StablingCosts, dbo.HorseVersion.StablingVatRate, dbo.HorseVersion.Comments
FROM         dbo.Horse INNER JOIN
                      dbo.HorseType ON dbo.Horse.Id = dbo.HorseType.Id INNER JOIN
                      dbo.HorseVersion ON dbo.Horse.Id = dbo.HorseVersion.HorseId INNER JOIN
                      dbo.HorseSex ON dbo.HorseVersion.HorseSexId = dbo.HorseSex.Id INNER JOIN
                      dbo.Location ON dbo.HorseVersion.LocationId = dbo.Location.Id INNER JOIN
                      dbo.Owner ON dbo.HorseVersion.InvoiceOwnerId = dbo.Owner.Id
WHERE     (dbo.HorseVersion.Id =
                          (SELECT     TOP (1) Id
                            FROM          dbo.HorseVersion AS HorseVersionSub
                            WHERE      (HorseId = dbo.Horse.Id)
                            ORDER BY Modified DESC))

Maar mochten er nog punten van kritiek zijn dan hoor ik die natuurlijk graag!

[ Voor 81% gewijzigd door Slagroom op 24-10-2006 11:49 ]

[whoami]

Slagroom schreef op dinsdag 24 oktober 2006 @ 11:38:
Maar de select in de subquery geeft maar één Modified terug... Modified is niet uniek. Dat zijn HorseId en Modified samen.

Kijk nog eens naar mijn subquery; da's een correlated subquery (de subquery wordt gejoined met een tabel buiten de subquery).

Justmental: afaik is dat in SQL Server idd zo.

[Slagroom]

Ow zo! Ik ziet het, thanks!

SQL:

SELECT     TOP (100) PERCENT dbo.Horse.Id, dbo.HorseVersion.Modified, dbo.HorseVersion.Number, dbo.HorseVersion.Name, 
                      dbo.HorseSex.Name AS HorseSexName, dbo.HorseVersion.Breeding, dbo.HorseVersion.StudbookNumber, dbo.HorseVersion.StudbookName, 
                      dbo.HorseVersion.BirthDate, dbo.HorseVersion.ChipNumber, dbo.HorseType.Name AS HorseTypeName, dbo.HorseVersion.OwnersShareCosts, 
                      dbo.Owner.Name AS OwnerName, dbo.Location.Name AS LocationName, dbo.HorseVersion.StablingCosts, dbo.HorseVersion.StablingVatRate, 
                      dbo.HorseVersion.Comments
FROM         dbo.Horse INNER JOIN
                      dbo.HorseType ON dbo.Horse.Id = dbo.HorseType.Id INNER JOIN
                      dbo.HorseVersion ON dbo.Horse.Id = dbo.HorseVersion.HorseId INNER JOIN
                      dbo.HorseSex ON dbo.HorseVersion.HorseSexId = dbo.HorseSex.Id INNER JOIN
                      dbo.Location ON dbo.HorseVersion.LocationId = dbo.Location.Id INNER JOIN
                      dbo.Owner ON dbo.HorseVersion.InvoiceOwnerId = dbo.Owner.Id
WHERE     (dbo.HorseVersion.Modified =
                          (SELECT     MAX(Modified) AS MaxModified
                            FROM          dbo.HorseVersion AS HorseVersionSub
                            WHERE      (HorseId = dbo.Horse.Id)))
ORDER BY dbo.HorseVersion.Modified DESC

Thanks!

[ Voor 96% gewijzigd door Slagroom op 24-10-2006 12:58 ]