[MySQL] Probleem met unique sort

[Bever]

Ik heb een lijstje met logs in de db staan. Er staat in: id, userid, page, ip, date.
Nu wil ik alle unieke ip's hebben met de laatste datum.
Hiervoor had ik:
SELECT * FROM logs GROUP BY ip;
Hierbij geeft ie dus de unieke ip's maar dan pakt ie de eerste, dus de oudste datum. En ik wil dus de nieuwste datum hebben. Ik heb al gebrobeert om er ORDER BY date DESC achter te zetten maar dan sorteerd ie pas als ie alle unieke ip's al heeft gepakt en niet ervoor.
Ik heb overal al naar dit probleem gezocht maar kan het nergens vinden. Kunnen jullie mij helpen?

[whoami]

Even in de manual kijken:

code:

select ip, max(datum) from tabel
GROUP BY ip

Basiskennis SQL en manual werk. Zie ook de links in de FAQ over SQL.

[Janoz]

Ok... whoami's oplossing bleek niet te werken aangezien de topicstarter ook de bijbehorende id nodig had

heropend om ervoor te zorgen dat het probleem niet inhoudelijk in LA behandeld wordt..

Ik zou als ik jou was trouwens ff op zoek gaan naar HAVING.

[whoami]

Op woensdag 12 juni 2002 14:53 schreef Janoz het volgende:
Ok... whoami's oplossing bleek niet te werken aangezien de topicstarter ook de bijbehorende id nodig had

code:

SELECT id, ip, max(datum) from tabel
GROUP BY id, ip

dan?

Ik zou als ik jou was trouwens ff op zoek gaan naar HAVING.

Having is een filter die je legt op de gegroepeerde rows.

[Verwijderd]

Een ID van 1 record terughalen binnen een stel gegroupte records gaat niet lukken, helaas.

[Janoz]

Het probleem is dat een MAX functie geen notie heeft bij welk record de uitkomst van de max hoort.

[Bever]

Op woensdag 12 juni 2002 14:59 schreef whoami het volgende:

[..]

code:

SELECT id, ip, max(datum) from tabel
GROUP BY id, ip

dan?
[..]

Having is een filter die je legt op de gegroepeerde rows.

Als je group by id,ip doet dan kijkt ie dus naar unieke id's en die zijn allemaal uniek, dus haalt hij alles op.

[Bever]

Op woensdag 12 juni 2002 15:01 schreef Janoz het volgende:
Het probleem is dat een MAX functie geen notie heeft bij welk record de uitkomst van de max hoort.

is er een andere functie waarmee dit wel mogelijk is?

[whoami]

En als je die ID weghaalt uit de GROUP BY list?

_{weet niet of dat wel gaat werken, maar je kunt het proberen}

[Verwijderd]

Op woensdag 12 juni 2002 15:03 schreef Bever het volgende:

[..]

is er een andere functie waarmee dit wel mogelijk is?

Oracle ondersteunt "first", waardoor de het eerste record binnen eenn group volgens de order by kan ophalen, maar MySQL ondersteunt dit niet, dus, nee, dat gaat niet lukken.

[Bever]

Op woensdag 12 juni 2002 15:08 schreef whoami het volgende:
En als je die ID weghaalt uit de GROUP BY list?

_{weet niet of dat wel gaat werken, maar je kunt het proberen}

Dan kom je weer op je eerste reactie uit, dus nee dat werkt ook niet, dan pakt ie alleen de nieuwste datum niet het nieuwste id.

[Bever]

Op woensdag 12 juni 2002 15:08 schreef MrX het volgende:

[..]

Oracle ondersteunt "first", waardoor de het eerste record binnen eenn group volgens de order by kan ophalen, maar MySQL ondersteunt dit niet, dus, nee, dat gaat niet lukken.

mmm dan moeten we eens kijken of er een andere oplossing mogelijk is.

[Verwijderd]

Je kunt dit in 2 queries oplossen. Eerst vraag je voor de IP de max datum op (zoals hierboven al stond). Vervolgens bouw je aan de hand daarvan een nieuwe query op, waarin de de specifieke records met die gegevens ophaalt.

code:

select * 
from logs
where 
  (ip = 10.0.0.1 and datum = 12-10-2002) OR
  (ip = 10.0.0.2 and datum = 11-10-2002) OR
  (ip = 10.0.0.3 and datum = 13-10-2002) OR
  ...

Verwacht hier alleen acceptabele performance van als er indexen staan op IP en datum of logs niet te groot is.

Enjoy

[Bever]

Daar ga ik even mee verder proberen, tnx

[ACM]

Een kleine performance tip is wellicht:

code:

select ...
where
datum between globaalkleinstedatumvdselectie and globaalgrootstedatumvdselectie
and (je or gedoe)

Mits je een index op die datum hebt en je een relatief klein deel uit je tabel hapt

[Feyd-Rautha]

zou het niet op deze manier kunnen?

code:

select e.ip from tabel e
where e.datum in (select max(f.datum) from tabel f
            where f.ip = e.ip)
group by e.ip

volgens mij moet je zoeken in de aard van correlated query's


ik kan ook helemaal fout zijn hoor

[whoami]

Op woensdag 12 juni 2002 15:24 schreef -Avalanche- het volgende:
zou het niet op deze manier kunnen?

code:

select ip from tabel e
where datum in (select max(datum) from tabel f
            where f.ip = e.ip)
group by ip

volgens mij moet je zoeken in de aard van correlated query's


ik kan ook helemaal fout zijn hoor

MySQL ondersteunt volgens mij nog steeds geen subqueries.

[Bever]

Op woensdag 12 juni 2002 15:24 schreef ACM het volgende:
Een kleine performance tip is wellicht:

code:

select ...
where
datum between globaalkleinste
datumvdselectie and globaalgroot
stedatumvdselectie
and (je or gedoe)

Mits je een index op die datum hebt en je een relatief klein deel uit je tabel hapt

Hij moet alleen de regels waar de datum >= actuele tijd - 5 minuten

Dus de logs van de laatste 5 minuten.
Werkende oplossing (PHP) (dit zijn nog alle logs):

code:

    $sql = mysql_query("SELECT ip,MAX(date)date FROM fotoboek_logs GROUP BY ip");
    while ($row = mysql_fetch_object($sql)){
    $sql_2 = mysql_query("SELECT * FROM fotoboek_logs WHERE ip='$row->ip' AND date='$row->date'");
    $id = @mysql_result($sql_2,0,"id");
    $ip = @mysql_result($sql_2,0,"ip");
    $page = @mysql_result($sql_2,0,"page");
    $date = @mysql_result($sql_2,0,"date");
    Echo "$id, $ip, $page, $date<br>";
    }

[Goodielover]

Dit is het zoveelste topic over data selecteren bij een max.
Nogmaals een werkende oplossing binnen MySQL. Zie mijn bijdrage in volgende topic:
[topic=467841]

[Verwijderd]

Sorry, heb per ongeluk mezelf weer gequote ipv ge-edit...

[Feyd-Rautha]

Op woensdag 12 juni 2002 15:52 schreef zellufs het volgende:

code:

SELECT    ip,
        max(f.datum) AS maximum
FROM    tabel AS e 
LEFT JOIN tabel AS f ON
        f.ip = e.ip
WHERE     e.datum = maximum
GROUP BY  ip

zoiets? schoot me net te binnen...

zoals ik dus al eerder had vermeld ...

[Verwijderd]

Op woensdag 12 juni 2002 15:49 schreef Goodielover het volgende:
Dit is het zoveelste topic over data selecteren bij een max.
Nogmaals een werkende oplossing binnen MySQL. Zie mijn bijdrage in volgende topic:
[topic=467841]

/me lol
Da's wel een ernstige truck, maar als het werkt, dan werkt het.

Ik vraag me echter ernstig af of dit nog performt, aangezien al het gebruik van indexen e.d. om zeep wordt geholpen hierdoor.

[Sjab-X]

Snap ik 't nou niet helemaal of komt er met Goodielover's 'smerige oplossing' maar 1 record uit? De bedoeling was toch dat alle ip adressen er uit kwamen, met de laatste datum?

Ik heb hetzelfde probleem maar dan in de volgende situatie
Tabel comment
Velden comment_id, user_id, add_date, photo_id

De bedoeling is om te sorteren op add_date (DESC) en dan de user_id, photo_id & add_date terug te krijgen, waarbij photo_id maar 1 keer voor mag komen (met de laatste add_date uiteraard).

De onderstaande query geeft alleen de laatste posting mee
SELECT
LEFT(MAX(concat(comment.add_date, comment.photo_id)), 19) AS Posttime, substring(MAX(concat(comment.add_date, comment.photo_id)), 20) AS Photo
FROM comment

Moet ik nou gewoon alle (??) records op gaan halen en het sorteer werk door PHP laten doen In MySQL moet dit toch ook op de een of andere manier kunnen...

[Goodielover]

Dan neem je dus extra velden op in je select en dan ook in je group by.
Ik rekende wel op een beetje zelfwerkzaamheid

[Sjab-X]

Ok, na enig stoeien ben zelfs ik er ook uit gekomen

De complete query (voor de gene die 't nog intereseerd):

code:

SELECT
 LEFT(MAX(concat(comment.add_date, comment.photo_id)), 19) AS add_date,
 substring(MAX(concat(comment.add_date, comment.photo_id)), 20) AS photo_id,
 photos.album_id,
 users.shortcode AS username,
 photos.text AS title
FROM
 comment, photos, users
WHERE
 users.user_id = comment.user_id AND
 photos.photo_id = comment.photo_id
GROUP BY
 comment.photo_id
ORDER BY
 add_date DESC

Thnx Goodielover voor de hulp!