[sql] select value bij laatste maand van tabel

[FvH]

Allereerst sorry voor de wazige titel, zou niet weten hoe het beter te omschrijven in 1 regel.

Goed

ik heb een tabel

code:

c_id        b_id     iMonth
1            101      9
1            102      10
2            103      8
2            104      9
2            190      11
etc. etc.

nu moet ik van 1 c_id alleen het b_id hebben van de laatste maand. Dus in geval van c_id 1 is dat maand 10 dus b_id = 102

Ik heb al van alles geprobeerd, maar ik krijg het niet voor elkaar. Distinct kan niet, want dat gaat over meerdere columns heen en bij andere opties loop ik met de order en group by commando's vast omdat daar dezelfde columns in moeten als in de select. Ik heb al met de MAX functie geprobeerd, maar kom daar ook niet verder mee.

Iemand een suggestie? db draait op MS SQL 2K, dus mag eventueel specifieke sql voor ms sql zijn.

[Gonadan]

FvH schreef op woensdag 24 januari 2007 @ 16:13:
Allereerst sorry voor de wazige titel, zou niet weten hoe het beter te omschrijven in 1 regel.

Goed

ik heb een tabel

code:

c_id        b_id     iMonth
1            101      9
1            102      10
2            103      8
2            104      9
2            190      11
etc. etc.

nu moet ik van 1 c_id alleen het b_id hebben van de laatste maand. Dus in geval van c_id 1 is dat maand 10 dus b_id = 102

Ik heb al van alles geprobeerd, maar ik krijg het niet voor elkaar. Distinct kan niet, want dat gaat over meerdere columns heen en bij andere opties loop ik met de order en group by commando's vast omdat daar dezelfde columns in moeten als in de select. Ik heb al met de MAX functie geprobeerd, maar kom daar ook niet verder mee.

Iemand een suggestie? db draait op MS SQL 2K, dus mag eventueel specifieke sql voor ms sql zijn.

Je hebt wel de functie FIRST, maar die werkt volgens mij niet op 2K.
Kan je geen subqueries gebruiken?

[The Eagle]

Probeer eens een inner join oid

[mithras]

Kan je niet iets doen als

SQL:

SELECT b_id FROM table WHERE iMonth = (SELECT MAX iMonth FROM table WHERE c_id = 1)

/snelle sql modus waar nog veel fouten inzitten maar het idee er wel is...

edit:
Een max is sneller dan "ORDER BY c_id DESC LIMIT 1"

[ Voor 21% gewijzigd door mithras op 24-01-2007 16:31 ]

[Verwijderd]

SQL:

SELECT bla1.c_id, bla1.b_id, bla1.imonth
FROM bla AS bla1
WHERE bla1.c_id = [Parametervariabele]
AND bla1.imonth = (SELECT MAX bla2.imonth FROM bla AS bla2 WHERE bla2.c_id = bla1.c_id);

Volgens mij zou dit moeten werken...

[Gonadan]

SQL:

SELECT b_id FROM table WHERE iMonth = (SELECT MAX(iMonth) AS iMonth FROM table GROUP BY c_id)

Zoiets?

[ Voor 228% gewijzigd door Gonadan op 24-01-2007 16:28 ]

[lier]

Voordat wij (enthousiaste SQL-ers) hier FvH's huiswerk op zitten te lossen...wat heeft de TS en wat lukt er precies niet ?

offtopic:
Dit lijkt mij redelijk elementaire SQL query...

[FvH]

Verwijderd schreef op woensdag 24 januari 2007 @ 16:23:

SQL:

SELECT bla1.c_id, bla1.b_id, bla1.imonth
FROM bla AS bla1
WHERE bla1.c_id = [Parametervariabele]
AND bla1.imonth = (SELECT MAX bla2.imonth FROM bla AS bla2 WHERE bla2.c_id = bla1.c_id);

Volgens mij zou dit moeten werken...

helaas...
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

[FvH]

lier schreef op woensdag 24 januari 2007 @ 16:29:
Voordat wij (enthousiaste SQL-ers) hier FvH's huiswerk op zitten te lossen...wat heeft de TS en wat lukt er precies niet ?

offtopic:
Dit lijkt mij redelijk elementaire SQL query...

het punt is dat ik alleen b_id nodig heb. Dus eigenlijk wil ik alleen b_id in mijn select hebben staan. Ik kan nu geen distinct gebruiken. Als een distinct op 1 kolom mogelijk is terwijl je ook nog andere kollomen kunt selecteren ben ik ook geholpen als je me kan vertellen hoe dit uit te voeren.
Voor zover ik weet gaat een distinct over alle columns heen die je opgeeft in de select.

[FvH]

Gonadan schreef op woensdag 24 januari 2007 @ 16:24:

SQL:

SELECT b_id FROM table WHERE iMonth = (SELECT MAX(iMonth) AS iMonth FROM table GROUP BY c_id)

Zoiets?

Alles wat in je group by staat moet ook in je select staan. Dus dit werkt niet.

[Gonadan]

FvH schreef op woensdag 24 januari 2007 @ 16:36:
Alles wat in je group by staat moet ook in je select staan. Dus dit werkt niet.

Nee, hoor, alles wat in je select staat moet in je group by staan, of door een aggregate functie behandeld worden.

En anders zet je toch gewoon c_id ook in de select? Maakt voor de werking weinig uit.

[justmental]

FvH schreef op woensdag 24 januari 2007 @ 16:33:
helaas...
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Wat heb je exact uitgevoerd dan?
Het lijkt me dat dit gewoon moet kunnen. Eventueel is dit nog om te schrijven naar een not exists, maar dat zou niet nodig moeten zijn.

[FvH]

Gonadan schreef op woensdag 24 januari 2007 @ 16:37:
[...]

Nee, hoor, alles wat in je select staat moet in je group by staan, of door een aggregate functie behandeld worden.

En anders zet je toch gewoon c_id ook in de select? Maakt voor de werking weinig uit.

Probleem is echter wel dat die subquery alle max-months terug geeft en je dus geen 1 record hebt waardoor de subquery ook als dusdanig gebruikt mag worden.

Ik moet dus wel voor alle b_id's terug krijgen, maar alleen die van de laatste maand van een c_id.

[Verwijderd]

SQL:

select
    tabel1.b_id
from
    tabel as tabel1
where
    tabel1.iMonth = (select max(tabel2.iMont) from tabel as tabel2 where tabel1.c_id = tabel2.c_id)

[Gonadan]

FvH schreef op woensdag 24 januari 2007 @ 16:42:
[...]


Probleem is echter wel dat die subquery alle max-months terug geeft en je dus geen 1 record hebt waardoor de subquery ook als dusdanig gebruikt mag worden.

Ik moet dus wel voor alle b_id's terug krijgen, maar alleen die van de laatste maand van een c_id.

Als ik mij niet vergis is dat precies wat mijn functie doet.

Hij pakt van elke c_id de hoogste iMonth.
En dan pakt hij van elke hoogste iMonth de b_id.
Dat is toch wat je wilde hebben?
Heb je hem eigenlijk al getest?

[FvH]

Ik denk dat ik mijn probleem als volgt moet uitleggen.

Het krijgen van een lijst van c_id's met alleen de max-maand erbij is niet het probleem. Die had ik al. Het is meer een probleem hoe dan vervolgens met dat result de b_id te krijgen.

[Verwijderd]

justmental schreef op woensdag 24 januari 2007 @ 16:38:
[...]

Wat heb je exact uitgevoerd dan?
Het lijkt me dat dit gewoon moet kunnen. Eventueel is dit nog om te schrijven naar een not exists, maar dat zou niet nodig moeten zijn.

In Access (niet dat dat maatgevend is ) werkt dat gewoon in ieder geval.

Verwijderd schreef op woensdag 24 januari 2007 @ 16:44:

SQL:

select
    tabel1.b_id
from
    tabel as tabel1
where
    tabel1.iMonth = (select max(tabel2.iMont) from tabel as tabel2 where tabel1.c_id = tabel2.c_id)

Dat is precies hetzelfde als mijn query, maar blijkbaar werkt dat niet?!

[Gonadan]

FvH schreef op woensdag 24 januari 2007 @ 16:45:
Ik denk dat ik mijn probleem als volgt moet uitleggen.

Het krijgen van een lijst van c_id's met alleen de max-maand erbij is niet het probleem. Die had ik al. Het is meer een probleem hoe dan vervolgens met dat result de b_id te krijgen.

Lees je de posts van anderen wel?
Je weet dat je met een subquery het tussenresultaat kunt gebruiken om een nieuw resultaat te krijgen?
Dus zoals in mijn query van alle hoogte iMonts per c_id de b_id's?

SQL:

SELECT b_id FROM table WHERE iMonth IN (SELECT MAX(iMonth) AS iMonth FROM table GROUP BY c_id)

Lees hem en probeer hem, anders weet ik het ook niet meer.
Ik weet niet of de syntax 100% correct is, ik kan hem hier niet testen. Maar het idee moet werken.
Syntax aangepast, dank aan justmental.

[ Voor 8% gewijzigd door Gonadan op 24-01-2007 16:54 ]

[foske]

Mis ik nu iets of werkt dit gewoon?

code:

SELECT TOP 1 b_id FROM table WHERE c_id = @id ORDER BY iMonth DESC

[justmental]

Gonadan, die van jou gaat niet werken, de subquery levert meerdere rijen op en is gekoppeld met een =.

Als het dbms het ondersteunt zou zoiets wel kunnen met (c_id, imonth) in (select c_id, max(imonth)...

[ Voor 33% gewijzigd door justmental op 24-01-2007 16:52 ]

[Gonadan]

justmental schreef op woensdag 24 januari 2007 @ 16:51:
Gonadan, die van jou gaat niet werken, de subquery levert meerdere rijen op en is gekoppeld met een =.

Ah inderdaad, gebruik dan IN

@justmental hieronder, ik was te snel
Al aangepast

[ Voor 29% gewijzigd door Gonadan op 24-01-2007 16:54 ]

[justmental]

Gonadan schreef op woensdag 24 januari 2007 @ 16:52:
Juist, en dat noemt men een join

Google eens naar subqueries, dan zie je hoe het werkt.

Je zou kunnen joinen vanuit de subquery als je dat bedoelt, maar dat doe je niet.

[FvH]

Gonadan schreef op woensdag 24 januari 2007 @ 16:52:
[...]

Ah inderdaad, gebruik dan IN

Het moet natuurlijk wel de max-maand van dat specifieke c_id zijn en niet een random result van die subquery

[DutchCommando]

Wat heb je zelf dan gemaakt. Want ik heb binnen twee minuten een query bedacht die prima werkt.

[NMe]

Waarom post je dit topic hier als je geen problemen hebt met een development tool maar met de beheersing van een taal? Waar hoort mijn topic?

DTE>>PRG