[MS SQL] Query probleem

[Verwijderd]

Na lang puzzelen en veel zoeken komen wij niet uit een op een eerste gezicht gemakkelijk SQL probleem. We hebben een MS SQL database met daarin de volgende (relevante) tabellen:

item (Id, Datum, ...) PK: Id
subItem(Id, ItemId, BeginTijd, ...) PK: Id, FK: ItemId -> item.Id

item - subitem is een one-to-many relatie.

(PK staat voor Primary Key en FK staat voor Foreign Key)

We willen graag het volgende weten van de database: alle (distinct) item.Id gesorteerd op item.Datum en als tweede gesorteerd op subItem.BeginTijd en waarbij de item.Datum in een range zit.

De volgende queries bleken niet goed te zijn:

code:

SELECT DISTINCT i.Id, i.Datum
FROM item i, subItem s
WHERE EXISTS(
    SELECT TOP 100 PERCENT i.Id, i.Datum, s.BeginTijd
    FROM item i, subitem s
    WHERE i.Datum BETWEEN '12-12-02' AND '12-12-05'
    AND i.Id = s.ItemId
    ORDER BY s.BeginTijd
)
AND i.Id = s.ItemId
ORDER BY i.Datum

Het blijkt dat top verplicht is als je een distinct uitvoert waarbij er een top in de subquery staat.
Daarnaast is het onmogelijk om een order by te doen, waarbij je het resultaat niet weergeeft in de select. Door deze tweede regel die MS SQL heeft, is het ons niet gelukt om goed te sorteren op, ten eerste item.Datum en daarnaast subItem.BeginTijd. Als we namelijk sorteren op Begintijd in de subquery en daarna sorteren op iets anders, dan is de eerste sortering ongedaan gemaakt. (zeer logisch). Echter kan je subItem.BeginTijd niet als resultaat teruggeven, want dan werkt de distinct op een andere manier. (Distinct is distinct op alle velden en we willen alleen de item.Id distinct hebben en de rest niet. Dus alle dubbele item.Id's mogen worden verwijderd, zolang er maar gesorteerd is op datum en secundair begintijd van het subitem.)

Hopelijk zijn wij duidelijk genoeg en kan iemand van jullie ons helpen met het oplossen van dit probleem.

[whoami]

Mjah....
Je wilt de unieke id's, maar je wilt wel die id's sorteren op datum en op tijd.

Echter, als er meerdere datums of tijden zijn voor hetzelfde id, wat wil je dan doen met dat ID ? Dat zal je dan toch moeten tonen?

(Trouwens, waarom zet je quotes rond die datums? Die datums zijn toch een datetime veld mag ik hopen? Het is beter als je die string CONVERT naar een datetime.)

[sig69]

Wat jij wil klopt niet. Je wil unieke id's van item, gesorteerd op datum EN gesorteerd op tijd van het/de subitem(s). En bij meerdere items? Dan moet je of item meerdere keren laten zien, of een keuze maken in welk subitem je neemt (TOP 1, MAX, whatever ).

[Verwijderd]

whoami schreef op donderdag 23 december 2004 @ 12:13:
Mjah....
Je wilt de unieke id's, maar je wilt wel die id's sorteren op datum en op tijd.

Ik wil de unieke id's van de table item hebben gesorteerd op datum. Als er twee id's op dezelfde dag zitten, dan moeten ze gesorteerd worden op de begintijd van het eerste subitem dat bij deze items hoort.

Echter, als er meerdere datums of tijden zijn voor hetzelfde id, wat wil je dan doen met dat ID ? Dat zal je dan toch moeten tonen?

Er kan per ID slechts een datum zijn. (id's zijn uniek) Mischien kan een voorbeeldje verhelderend zijn:

code:

item.id    item.datum    subitem.begintijd
1             vandaag              12.00
2             vandaag              10.00
3             morgen               13.00
3             morgen               14.00
4             gisteren              15.00
5             overmorgen         23.00

Het moet er uiteindelijk zo uit komen te zien:

code:

Id
4
2
1
3
5

(Trouwens, waarom zet je quotes rond die datums? Die datums zijn toch een datetime veld mag ik hopen? Het is beter als je die string CONVERT naar een datetime.)

Jaa.. misschien heb je wel gelijk, maar die tijd was vooral om te testen. We werken met stored procedures en daarbij gebruiken variabelen.

Wat jij wil klopt niet. Je wil unieke id's van item, gesorteerd op datum EN gesorteerd op tijd van het/de subitem(s). En bij meerdere items? Dan moet je of item meerdere keren laten zien, of een keuze maken in welk subitem je neemt (TOP 1, MAX, whatever ).

De subitem die als eerste begint, die telt.

[ Voor 12% gewijzigd door Verwijderd op 23-12-2004 12:27 ]

[whoami]

Verwijderd schreef op donderdag 23 december 2004 @ 12:25:
[...]

Ik wil de unieke id's van de table item hebben gesorteerd op datum. Als er twee id's op dezelfde dag zitten, dan moeten ze gesorteerd worden op de begintijd van het eerste subitem dat bij deze items hoort.

Maar als je die tijd niet toont, wat voor zin heeft het dan om te sorteren op tijd ?

Trouwens, SQL is er om gegevens op te halen, gegevens te manipuleren etc... De formattering ervan moet je niet in sql doen.

[ Voor 15% gewijzigd door whoami op 23-12-2004 12:36 ]

[Verwijderd]

whoami schreef op donderdag 23 december 2004 @ 12:35:
[...]

Maar als je die tijd niet toont, wat voor zin heeft het dan om te sorteren op tijd ?

Dat wil de baas.

Trouwens, SQL is er om gegevens op te halen, gegevens te manipuleren etc... De formattering ervan moet je niet in sql doen.

Dat ben ik niet geheel met je eens. Aangezien SQL databases vaak een goed geordende datastructuur hebben, gaat het sorteren van dit soort dingen naar verwachting in O(n log n) tijd. Als we dat doen in onze applicatie zelf, dan zullen we eerst weer een datastructuur moeten maken en dan deze gaan sortoren. Dit neemt gewoonweg veels te veel tijd in beslag en is onnodig.

[whoami]

Sorteren, dat moet je in SQL doen, dat ben ik met je eens.
Ik heb het over het formatteren van de opgehaalde gegevens; bv. Het id slechts 1x laten zien als je herhalende items hebt voor datzelfde id.

[whoami]

Verwijderd schreef op donderdag 23 december 2004 @ 12:25:
[...]

Ik wil de unieke id's van de table item hebben gesorteerd op datum. Als er twee id's op dezelfde dag zitten, dan moeten ze gesorteerd worden op de begintijd van het eerste subitem dat bij deze items hoort.

Dus eigenlijk wil je, anders gezegd, de items gesorteerd op datum met -als er meerdere zijn voor dezelfde datum- de laagste tijd ?
ook niet.
Ik zie eigenlijk geen logica in je voorbeeld.

[ Voor 8% gewijzigd door whoami op 23-12-2004 12:56 ]

[Verwijderd]

whoami schreef op donderdag 23 december 2004 @ 12:54:
[...]

Dus eigenlijk wil je, anders gezegd, de items gesorteerd op datum met -als er meerdere zijn voor dezelfde datum- de laagste tijd ?

Zoiets.. als er meerdere zijn voor dezelfde datum, dan wil ik ze gesorteerd op begintijd van de subitems. dus:

id datum tijd
1 vandaag 09.00
1 vandaag 10.00
1 vandaag 11.00
1 vandaag 11.00
1 vandaag 12.00
1 vandaag 13.00
2 vandaag 08.00
2 vandaag 12.00
2 vandaag 23.00

moet worden:

id datum
2 vandaag
1 vandaag

[whoami]

Kan je dan niet zoiets doen :

code:

SELECT DISTINCT id, datum
FROM ( SELECT id, datum, tijd
            FROM subitems, items
            WHERE ...
            ORDER BY datum, tijd )

[Dido]

Ik mis een GROUP BY in je query...

Iets als

code:

SELECT a.item, a.datum, MIN(b.tijd)
FROM tabel1 a JOIN tabel2 b ON a.id=b.id
GROUP BY a.item, a.datum
ORDER BY 2 ASC, 3 ASC

waarbij je dan je derde kolom negeert (desnoods applicatief weggooit voordat je het presenteert).

[P_de_B]

Dit moet toch gewoon werken?

code:

SELECT I.Id, I.Datum, MIN(S.BeginTijd) as BeginTijd
FROM Item I
INNER JOIN SubItems S ON I.Id =  S.ItemId
GROUP BY I.Id, I.Datum
ORDER BY I.Datum, MIN(S.BeginTijd)

edit:

Dido

edit2:
Niet gezien dat eigenlijk BeginTijd niet in de output moet.
Dan snap ik de query niet echt denk ik, maar onderstaande moet werken

code:

SELECT I.Id, I.Datum
FROM Item I
INNER JOIN SubItems S ON I.Id =  S.ItemId
GROUP BY I.Id, I.Datum
ORDER BY I.Datum, MIN(S.BeginTijd)

[ Voor 54% gewijzigd door P_de_B op 23-12-2004 13:25 ]

[Verwijderd]

P_de_B schreef op donderdag 23 december 2004 @ 13:20:

Dan snap ik de query niet echt denk ik, maar onderstaande moet werken.

code:

SELECT I.Id, I.Datum
FROM Item I
INNER JOIN SubItems S ON I.Id =  S.ItemId
GROUP BY I.Id, I.Datum
ORDER BY I.Datum, MIN(S.BeginTijd)

Precies. Zoiets dacht ik ook. Dit werkt alleen niet, omdat alles in ORDER BY in de select moet staan. Het is echter niet zo'n probleem als s.begintijd in de select staat. Dat gooien we aan applicatie kant gewoon weg.

Deze query (iets aangepast) werkt idd, alleen werkt het niet correct. Ik zal wat output laten zien van MSSQL:

code:

resultaat:
id           datum            tijd
565        2003-11-21  09:30:00.000
566        2003-11-21 13:45:00.000

de waardes in subitem:
itemid     begintijd
565          09:30:00.000
566          16:00:00.000
566          16:30:00.000
566          19:00:00.000
566          08:00:00.000
566          13:45:00.000
566          21:00:00.000

Id 566 heeft een lagere begintijd....

[P_de_B]

Verwijderd schreef op donderdag 23 december 2004 @ 13:40:
[...]


Precies. Zoiets dacht ik ook. Dit werkt alleen niet, omdat alles in ORDER BY in de select moet staan.

Niet in Microsoft SQL Server

Voor de rest, weet je 100% zeker dat je de query goed heb overgenomen? Volgens mij moet hij goed werken. Ik heb even getest met jouw voorbeeldje en ik krijg idd eerst id 566 en dan 565.

[Verwijderd]

Hoo.. je hebt gelijk.. Ik zat verkeerd te kijken..

Thanx!