[MySQL] unieke waarden uit kolom filteren

[Tweeker]

Stel,

ik heb een tabel tbl

code:

     x   y   z
    -------------
     1   2   1
     1   3   4
     1   4   2
     1   4   1
     2   9   3
     2   7   2

x, y en z kunnen alles zijn.

en ik wil daar uit hebben de unieke x-en met de laagste z, met de bijbehorende y

kortom, de uitkomst moet zijn

code:

     x   y   z
    -------------
     1   2   1
     2   7   2

En dit alles in MySQL 4.1.8.
Ik krijg het niet voor elkaar, en ik wil het eigenlijk niet met een geprogrammeerde (PHP) oplossing doen omdat we het hier over een 1000+ records hebben.

Hoe kan ik dit het beste aanpakken, of is het eigenlijk onmogelijk?

[ Voor 13% gewijzigd door Tweeker op 14-04-2006 14:36 ]

[Upsal]

Heb je het al geprobeert met DISTINCT ?

[ Voor 46% gewijzigd door Upsal op 14-04-2006 14:25 ]

[Tweeker]

uiteraard dat als eerste, maar die y moet ik ook hebben en die gooit roet in het eten.

[RobIII]

Dan maak je 2 queries en knoop je ze aan elkaar met een UNION?

[ Voor 31% gewijzigd door RobIII op 14-04-2006 14:29 ]

[Tweeker]

Het vervelende is dat x niet alleen 1 en 2 is maar alles kan zijn.

[Pete]

Het is jammer dat je nog geen Mysql 5.* hebt want dat zou het waarschijnlijk met 1 query te doen zijn. Nu kan dat volgens mij niet. Nu zou je bijv. kunnen doen

SQL:

SELECT x, MIN(z) as minz 
FROM table 
GROUP BY x;

hierna kun je de y-waarden erbij zoeken door de query:

SQL:

SELECT y
FROM table
WHERE x = '{$row['x']}' AND z = '{$row['minz']}';

als je mysql 5.* had gehad had je meteen kunnen joinen op de subquery

edit: @_js_ Dat lijkt ook op een oplossing. Echter kunnen er geen subqueries gebruikt worden

[ Voor 22% gewijzigd door Pete op 14-04-2006 14:51 ]

[_js_]

SQL:

SELECT x, min( (

SELECT y
FROM tbl t2
WHERE t1.x = t2.x
AND t1.z = t2.z
ORDER BY y ASC 
LIMIT 1 
) ) AS y, min( z ) AS z
FROM tbl t1
GROUP BY x

[Tweeker]

dat weet ik, maar het is een bestaand systeem met bestaande code en waar niet zomaar iets in veranderd mag worden, dus ook geen mysql5, helaas

ik ben net nog eens gaan klooien en het lijkt er op dat dit de truuk doet:

SQL:

SELECT x, min(z), y
FROM tbl
GROUP BY x
ORDER BY z

maar syntactisch gezien lijkt mij dit niet correct

[ Voor 5% gewijzigd door Tweeker op 14-04-2006 14:51 ]

[RobIII]

GOTTER schreef op vrijdag 14 april 2006 @ 14:50:
dat weet ik, maar het is een bestaand systeem met bestaande code en waar niet zomaar iets in veranderd mag worden, dus ook geen mysql5, helaas

ik ben net nog eens gaan klooien en het lijkt er op dat dit de truuk doet:

SQL:

SELECT x, min(z), y
FROM tbl
GROUP BY x
ORDER BY z

maar syntactisch gezien lijkt mij dit niet correct

Dit lijkt me toch heel andere resultaten terug te geven dan waar je in eerste instantie om vroeg...

[Tweeker]

klopt, het leek even te werken, maar het geeft na contole toch niet de juiste waarden terug.

Het leek niet correct en is het ook niet

[ Voor 20% gewijzigd door Tweeker op 14-04-2006 15:01 ]

[Janoz]

Klopt. Het is enkel mysql die op die code geen foutmelding geeft. Je zou eens kunnen kijken of je iets kunt doen met HAVING.

[BCC]

SQL:

SELECT a.x,b.y,b.z FROM `tbl` a, `tbl` b
WHERE a.x = b.x  AND b.z <= a.z
GROUP BY a.x

Dit werkt , maar dit moet je nog wel even testen

Door de < sorteert hij de z van klein naar groot, voordat je de de GROUP BY doet om de "dubbele" x-en eruit te halen.

[ Voor 93% gewijzigd door BCC op 14-04-2006 15:35 ]

[Tweeker]

enige is dat de query wel 6 keer zo traag wordt

[BCC]

GOTTER schreef op vrijdag 14 april 2006 @ 15:27:
enige is dat de query wel 6 keer zo traag wordt

6 keer lijkt me wel erg veel. Maar dan moet je een index bouwen op x Maar een andere oplossing dan dit is er volgens mij niet (zonder subqueries/PHP tenminste).

[Soultaker]

Janoz schreef op vrijdag 14 april 2006 @ 15:09:
Klopt. Het is enkel mysql die op die code geen foutmelding geeft.

Er is niets mis met die SQL code hoor. Het doet niet wat de TS wil, maar een foutmelding is niet op z'n plaats.

edit:
Of doel je op het gebruik van y zonder aggregatiefunctie? Dat is niet standaard, maar er zijn wel meer databases die dat ondersteunen. (Het is wel wat dubieus, inderdaad.)

Je zou eens kunnen kijken of je iets kunt doen met HAVING.

Dat helpt niet, want daarmee kun je alleen maar filteren en niet kiezen welke waarden je samenneemt met 'x'. Je moet ofwel voor het groeperen de juiste waarden selecteren (wat niet lijkt te kunnen) of ze er later bijzoeken met een extra query (maar dat kan MySQL 4 weer niet; in één query tenminste).

BCC schreef op vrijdag 14 april 2006 @ 15:16:

SQL:

SELECT a.x,b.y,b.z FROM `tbl` a, `tbl` b
WHERE a.x = b.x  AND b.z < a.z
GROUP BY a.x

Dit werkt , maar dit moet je nog wel even testen

Door de < sorteert hij de z van klein naar groot, voordat je de de GROUP BY doet om de "dubbele" x-en eruit te halen.

Volgens mij klopt daar niets van. Het werkt misschien toevallig voor het voorbeeld, maar de enige garantie die je hebt is dat de opgeleverde z niet de grootste is. Als er voor bepaalde waarden van x maar één regel in de tabel zit lever je helemaal niets op.

Mij lijkt de conclusie dus dat het niet in één query kan. Je zult eerst de juiste x en z moeten opzoeken en vervolgens de bijbehorende y's. Merk op dat dat ook nog conflicten kan opleveren, zoals in de voorbeeldtabel ook te zien is, omdat bijvoorbeeld x=1 en z=1 zowel y=2 als y=4 toelaat.

BCC schreef op vrijdag 14 april 2006 @ 15:35:
Klopt, het moet a.z <= b.z zijn.

Dan is 'ie net zo goed stuk.

Maar waarom dacht je dat ik erbij had gezegd dat je hij het zeker nog moest testen

Nou, als je er bij zet "Dit werkt!" dan verwacht ik dat het, erm... werkt. En dat doet het niet.

[ Voor 31% gewijzigd door Soultaker op 14-04-2006 15:39 ]

[BCC]

Soultaker schreef op vrijdag 14 april 2006 @ 15:33:
Volgens mij klopt daar niets van. Het werkt misschien toevallig voor het voorbeeld, maar de enige garantie die je hebt is dat de opgeleverde z niet de grootste is. Als er voor bepaalde waarden van x maar één regel in de tabel zit lever je helemaal niets op.

Klopt, het moet zowieso a.z <= b.z zijn, maar dan werkt het niet meer.
Maar waarom dacht je dat ik erbij had gezegd dat je hij het zeker nog moest testen
Ik bedenk me nu dat het inderdaad ook mis kan gaan als x niet gesorteerd is. mybad Maar zoiets moet toch kunnen.. ik puzzel even verder...

[ Voor 34% gewijzigd door BCC op 14-04-2006 15:49 ]

[Janoz]

Soultaker schreef op vrijdag 14 april 2006 @ 15:33:
[...]

Er is niets mis met die SQL code hoor. Het doet niet wat de TS wil, maar een foutmelding is niet op z'n plaats.

edit:
Of doel je op het gebruik van y zonder aggregatiefunctie? Dat is niet standaard, maar er zijn wel meer databases die dat ondersteunen. (Het is wel wat dubieus, inderdaad.)

Ik doel inderdaad op de niet geaggregeerde kolom die ook niet opgenomen is in de group by. In dat geval is niet gedefinieerd welke je terug wilt geven. Bij een max of een min is er misschien bij sommige gebruikers nog de gedachte dat het record erbij gezocht wordt, maar wanneer je een sum of avg gebruikt dan zal het ook voor die mensen wel duidelijk worden

[...]

Dat helpt niet, want daarmee kun je alleen maar filteren en niet kiezen welke waarden je samenneemt met 'x'. Je moet ofwel voor het groeperen de juiste waarden selecteren (wat niet lijkt te kunnen) of ze er later bijzoeken met een extra query (maar dat kan MySQL 4 weer niet; in één query tenminste).

Ik heb er verder nog niet echt over nagedacht. Zolang je nog geen subqueries kunt gebruiken en je iets wilt met een record dat een maximum waarde heeft zou je dit vaak in de richting van een having kunnen vinden. Later kwam ik er echter achter dat het in dit geval nogal lastig gaat worden aangezien je meerdere maxima wilt hebben (1 per x).

[w!mz]

Ik vond dit wel een interesant probleem, dus ik dacht 'goh, laat ik ook eens wat proberen bij te dragen :P'

Dit scriptje heb ik gemaakt; om de waardes te behalen die je wilde

PHP:

<?php
$query = mysql_query("SELECT x, MIN(z) from tbl group by x");

while($row = mysql_fetch_assoc($query))
    {
        echo $z = $row['MIN(z)']; echo "<br>";
        echo $x = $row['x']; echo "<br>";
        $query2 = mysql_query("SELECT x, y, z from tbl where z = '$z' AND x = '$x' group by y asc") or die (mysql_error());
        while($row2 = mysql_fetch_assoc($query2))
        {
            echo $row2['x'] . " " . $row2['y'] . " " . $row2['z'] . "<p>";
            break;
        }
    }

?>

<pre>
     x   y   z
    -------------
     1   2   1
     2   7   2
</pre>

Wat gebeurt er? Ik select per x op de laagste z in de eerste query;
Bij de laagste z kunnen meerdere y's passen; ik haal gwn alle y's op, sorteer op diezelfde y's en druk ze af, gwn breaken na eerste afgedrukte rij; dit stukje code geeft de results die tussen de pre tags staan; de uitkomst is correct bij deze de tabel 'tbl' die TS gaf;

PH smit doet eigenlijk hetzelfde naja, ik kruip wel weer terug onder m'n steen

[ Voor 17% gewijzigd door w!mz op 14-04-2006 16:43 ]

[Tweeker]

Soms heb ik een pesthekel aan alle verschillende SQL dialecten. Maar ik ga het denk ik maar fot the time being codetechnisch oplossen, dis de tabel ophalen en dan de records naar een andere array pushen als de x nog niet in dat array voorkomt.

Het is de oplossing di ik graag zie, normaal heb ik het liefst alle datavergaring in 1 query, maar het moet maar even.

[Swerfer]

GOTTER schreef op vrijdag 14 april 2006 @ 14:22:
Stel,

ik heb een tabel tbl

code:

     x   y   z
    -------------
     1   2   1
     1   3   4
     1   4   2
     1   4   1
     2   9   3
     2   7   2

x, y en z kunnen alles zijn.

en ik wil daar uit hebben de unieke x-en met de laagste z, met de bijbehorende y

kortom, de uitkomst moet zijn

code:

     x   y   z
    -------------
     1   2   1
     2   7   2

En dit alles in MySQL 4.1.8.
Ik krijg het niet voor elkaar, en ik wil het eigenlijk niet met een geprogrammeerde (PHP) oplossing doen omdat we het hier over een 1000+ records hebben.

Hoe kan ik dit het beste aanpakken, of is het eigenlijk onmogelijk?

Wat bedoel je eigenlijk met bijbehorende Y?

Volgens mij kan je dan ook onderstaande uitkomst krijgen:

code:

     x   y   z
    -------------
     1   4   1
     2   7   2

Of wil je de eerste Z gebruiken die in de tabel voorkomt?

[Tweeker]

De eerste.

Er zijn nog extra randvoorwaarden die de sortering bepalen, maar die zijn niet van belang voor dit probleem.

[Verwijderd]

De TS gebruikt MySQL 4.1.8, subqueries zijn wel degelijk ondersteund hoor in die versie.