[SQL] Hoe een veld ophalen waarop je niet wilt group by'en

[Gomez12]

Ik heb een tabel met bijv het volgende :

ImportID RelationID ImportDate
1 100 30-5-2013
2 101 30-5-2013
3 100 31-5-2014

Nu wil ik het importid hebben wat hoort bij de laatste importdate per relatie, maar hoe krijg ik die zonder een subquery oid?

Ik kan wel iets verzinnen in de trant van :

SQL:

select a.ImportID, a.RelationID
from a
inner join (select RelationID, max(importDate) from a group by RelationID) as b on (b.RelationID=a.RelationID and b.ImportDate=a.ImportDate)

Maar omdat ik de ImportID eigenlijk enkel maar nodig heb om verder te joinen heb ik eigenlijk het liefste op een zo simpel mogelijke manier het laatste importid van een bepaalde relatie.
En een self-inner-join vind ik nou niet echt het meest fraaie...

Wat zie ik hier over het hoofd? Want eigenlijk lijkt het mij een redelijke standaard vraag die gewoon in 1 query opgelost moet kunnen worden.

Maar als ik :

SQL:

select a.ImportId, a.RelationID, max(a.ImportDate)
from a
group by a.ImportId, a.RelationID

Doe dan krijg ik ik alle importids terwijl ik er maar 1 per relatie wil hebben (de laatste).

[sig69]

Je doet het verkeerd om:

SQL:

select a.ImportDate, a.RelationID, max(a.ImportId) 
from a 
group by a.ImportDate, a.RelationID

[creator1988]

Gomez12 schreef op vrijdag 31 mei 2013 @ 10:35:
Ik heb een tabel met bijv het volgende :

ImportID RelationID ImportDate
1 100 30-5-2013
2 101 30-5-2013
3 100 31-5-2014

Nu wil ik het importid hebben wat hoort bij de laatste importdate per relatie, maar hoe krijg ik die zonder een subquery oid?

Ik kan wel iets verzinnen in de trant van :

SQL:

select a.ImportID, a.RelationID
from a
inner join (select RelationID, max(importDate) from a group by RelationID) as b on (b.RelationID=a.RelationID and b.ImportDate=a.ImportDate)

Maar omdat ik de ImportID eigenlijk enkel maar nodig heb om verder te joinen heb ik eigenlijk het liefste op een zo simpel mogelijke manier het laatste importid van een bepaalde relatie.
En een self-inner-join vind ik nou niet echt het meest fraaie...

Wat zie ik hier over het hoofd? Want eigenlijk lijkt het mij een redelijke standaard vraag die gewoon in 1 query opgelost moet kunnen worden.

Maar als ik :

SQL:

select a.ImportId, a.RelationID, max(a.ImportDate)
from a
group by a.ImportId, a.RelationID

Doe dan krijg ik ik alle importids terwijl ik er maar 1 per relatie wil hebben (de laatste).

SQL:

select MAX(a.importId), a.RelationID, max(a.ImportDate)
FROM a
GROUP BY a.RelationID

?

[Psychokiller]

sig69 schreef op vrijdag 31 mei 2013 @ 10:48:
Je doet het verkeerd om:

SQL:

select a.ImportDate, a.RelationID, max(a.ImportId) 
from a 
group by a.ImportDate, a.RelationID

Daarmee krijg je per datum een importid terug en dus niet 1 per relatie.

creator1988 schreef op vrijdag 31 mei 2013 @ 10:48:
[...]

SQL:

select MAX(a.importId), a.RelationID, max(a.ImportDate)
FROM a
GROUP BY a.RelationID

?

Dat gaat goed zolang de importdate altijd oplopend is. Normaal gesproken klopt dat ook wel, maar als er een lagere importdate zou zijn toegevoegd met de hand ofzo, dan krijg je dus een mismatch.

Voor zover ik weet ontkom je niet aan een join met de max importdate als je datum niet altijd oplopend is.

[Gomez12]

creator1988 schreef op vrijdag 31 mei 2013 @ 10:48:
[...]

SQL:

select MAX(a.importId), a.RelationID, max(a.ImportDate)
FROM a
GROUP BY a.RelationID

?

importid zijn niet gegarandeerd oplopend.
Dus pakt hij eerst het maximum importid en zoekt hij daarna de maximum importdate erbij dan gaat het fout. Zou hij eerst de maximum importdate en daarna de maximum importid pakken dan zou het wel goed gaan, maar ik heb geen idee hoe ik situatie 2 afdwing.

[Russel88]

Volgens mij kun je die niet oplossen zonder een join of een subquery.
Ik zou het oplossen met een extra join.

[T-8one]

In geval van Oracle zoek eens op analytic functions, volgens mij moet je daar een heel eind mee komen.

[Rotterdammertje]

T-8one schreef op vrijdag 31 mei 2013 @ 15:50:
In geval van Oracle zoek eens op analytic functions, volgens mij moet je daar een heel eind mee komen.

Yep. Zowel Oracle als SQL-Server ondersteunen de analytic function "LAST_VALUE", die precies doet wat je wil.

[JaQ]

Bij analytic functies denk ik aan leap over en pivot, maar dit is een typische kandidaat voor having IMHO Dat is toch geen analytische functie?

[Onbekend]

Je hebt een ImportID en een ImportDate. Is de ImportID altijd oplopend en de ImportDate ook?

Zo ja, dan blijft het een simpele query:

SELECT RelationID, MAX(ImportID), MAX(ImportDate)
FROM Table
GROUP BY RelationID

[Gomez12]

Rotterdammertje schreef op vrijdag 31 mei 2013 @ 15:56:
[...]


Yep. Zowel Oracle als SQL-Server ondersteunen de analytic function "LAST_VALUE", die precies doet wat je wil.

Het is SQL server, dus dan wordt het LAST_VALUE thnx.

JaQ schreef op vrijdag 31 mei 2013 @ 22:48:
Bij analytic functies denk ik aan leap over en pivot, maar dit is een typische kandidaat voor having IMHO Dat is toch geen analytische functie?

Kan je wellicht uitleggen met bijv een voorbeeld hoe je dit met having wil bereiken?
Ik zie het namelijk niet...

Onbekend schreef op vrijdag 31 mei 2013 @ 22:53:
Je hebt een ImportID en een ImportDate. Is de ImportID altijd oplopend en de ImportDate ook?

ImportID is niet gegarandeerd oplopend (ene wordt aan begin van de job gezet, andere wordt aan het einde van de job gezet en er lopen meerdere jobs parallel)

Toch maar eens uitzoeken dan hoeveel het wegdoen van die id me qua opslagruimte gaat kosten.
Ter uitleg : een job kan meerdere miljoenen records bevatten, 100'en jobs per dag, daarom was het idee om de foreign key zo klein mogelijk te houden (een id) maar als het zoveel moeite kost om die key eruit te trekken dan is het wellicht toch wel de extra storage waard (qua performance maakt het vrijwel niets uit, er ligt een index op alle drie de velden dus alles komt uit de index, alleen de onderhoudbaarheid houdt ik liever ook laag dan dat iedereen 2x moet kijken wat dit precies doet)

[gvdh]

Ik zou eerder zoiets proberen:

SQL:

select max(a.ImportID), a.RelationID
from a
group by a.RelationID
having a.ImportDate = (
  select max(a2.ImportDate)
  from a a2
  where a2.RelationID = a.RelationID)

Op deze manier zou je de hoogste ImportID moeten krijgen bij de hoogste ImportDate van elke RelationID. Als je een index hebt op RelationID zou dit performant moeten zijn.

[Onbekend]

Waarom pas je het vullen van de tabel dan niet aan? Als je flink wat queries per dag hebt, dan zullen de queries bij veel gegevens op een gegeven moment een stuk trager gaan.

Dan krijg je dus gewoon de primary key op RelationID, en is een eenvoudige select ook snel uitgevoerd.

[CaVeFiSh]

Voor dit soort situaties zou je ook een APPLY kunnen gebruiken zoals dit:

code:

SELECT a.ImportID ,a.RelationID ,a.ImportDate ,c.LastImportID
FROM jouwtabel AS a
OUTER APPLY (SELECT TOP 1 b.ImportID AS LastImportID
                           FROM jouwtabel AS b
                           WHERE b.RelationID = a.RelationID
                           ORDER BY  b.ImportDate DESC) c

Een andere optie is uiteraard de LAST_VALUE optie zoals hierboven genoemd. Je keuze zou je moeten baseren op het executionplan van beide queries.