[MySQL] group by/order by probleem

[Verwijderd]

Ik heb de volgende log tabel:
tbl_log:

• log_id
• gebruiker
• datum
• locatie

Deze tabel registreert per gebruiker waar iemand is ingelogd en op welk tijdstip. De records zien er als volgt uit:

1, kees, 2007-11-01 , 192.168.2.13
2, henk, 2007-11-02 , 192.168.2.11
3, kees, 2007-11-03 , 192.168.2.14
4, jan, 2007-11-04 , 192.168.2.13
5, kees, 2007-11-04 , 192.168.2.33
6, jan, 2007-11-05 , 192.168.2.33

Uit deze tabel wil ik nu een overzicht (records) hebben die aangeeft op welke locatie elke gebruiker voor het laatst is ingelogd. Het resultaat moet er als volgt uit zien:
2, henk, 2007-11-02 , 192.168.2.11
5, kees, 2007-11-04 , 192.168.2.33
6, jan, 2007-11-05 , 192.168.2.33

Het volgende heb ik geprobeerd:

SELECT * FROM tbl_log GROUP BY gebruiker ORDER BY datum DESC

Alleen sorteert dit juist het resultaat. Hoe kan ik dit met een SQL statement voor elkaar krijgen?

[ Voor 8% gewijzigd door Verwijderd op 06-11-2007 22:53 ]

[RobIII]

Hier al eens gekeken? Iets met aggregate functies (zoals Max() ) enzo

Verwijderd schreef op dinsdag 06 november 2007 @ 22:50:

Het volgende heb ik geprobeerd:

SELECT * FROM tbl_log GROUP BY gebruiker ORDER BY datum DESC

Het is dat je dat er nog vlug bijgezet hebt, anders zat je topic nu op slot We zien nu eenmaal graag wat je zélf al geprobeerd hebt enzo Anyway, ik zie ook dat je nieuw bent hier. In dat geval dus van harte welkom maar toch even voor de zekerheid: neem even onze Programming Beleid Quickstart en dan meteen ook ons Algemeen beleid door

Maar dan nog een andere kleine kanttekening: Als je code (of query's) post, gebruik dan code tags.

[ Voor 97% gewijzigd door RobIII op 06-11-2007 22:57 ]

[aukema]

laat maar.. te veel moeite

[ Voor 84% gewijzigd door aukema op 06-11-2007 23:03 ]

[RobIII]

aukema schreef op dinsdag 06 november 2007 @ 22:59:
Iets van

select
log_id,
gebruiker,
max(datum),
locatie
from tbl_log
order by max(datum)
group by log_id

Dan ga jij die FAQ ook maar eens gauw lezen (en kijk dan ook even naar code tags, net als TS). Je group by is sowieso al niet compleet. Het stukje waar ik specifiek op doel:

quote: http://gathering.tweakers..._messages/1105378#groupby

...een hele brakke database, die deze laatste constructie wel toestaat. En volgens de handleiding is het 'by design' dat je vervolgens random waardes in kolom B aantreft. Don't do it.

[ Voor 30% gewijzigd door RobIII op 06-11-2007 23:03 ]

[Verwijderd]

Ik gok dat je bedoelt met zoiets:

SQL:

SELECT log_id, gebruiker, MAX(datum) as maxdatum, locatie FROM tbl_log GROUP BY gebruiker ORDER BY maxdatum

Dit geeft het volgende:
2 henk 2007-11-02 192.168.2.11
1 kees 2007-11-04 192.168.2.13
4 jan 2007-11-05 192.168.2.1

Dit geeft geen juiste log_id's met bij behoorde datum. Dit komt volgens mij omdat MAX() een maximum van elke group bepaald.
Ik zou iets moeten hebben van

SQL:

SELECT * FROM DISTINCT 

maar dit mag niet!

[CoolGamer]

Dit geeft volgens mij wel het juiste resultaat:

SQL:

SELECT gebruiker, locatie, datum
FROM tbl_log g1
WHERE datum = (
    SELECT MAX( datum )
    FROM tbl_log g2
    WHERE g2.gebruiker = g1.gebruiker );

Volgens mij is de vraag niet geschikt voor een query met een GROUP BY.

[RobIII]

Ik heb effe gespeeld, en het enige wat me 'dwars' zit is dat je het id en ip wil hebben; zodra die verschillend zijn gaat je 'group by' natuurlijk niet meer werken (en een max(locatie) lijkt me ook niet echt zinnig).

Met wat subquery's ben je er volgens mij wel zo; (effe quick'n'dirty)

SQL:

select log_id, gebruiker, datum, locatie    --unieke records
from tbl_log
where log_id in (
    select max(log_id) as id        --unieke (max) log-id's
    from tbl_log
    where gebruiker in (
        Select gebruiker        --unieke gebruikers
        From tbl_log
        group by gebruiker
    )
    group by gebruiker
)
order by log_id

Granted, het leek op het eerste gezicht makkelijker dan het is
Anyway; het is al laat en volgens mij kijk ik er ontzettend langs

[ Voor 11% gewijzigd door RobIII op 06-11-2007 23:54 ]

[CoolGamer]

SQL:

select log_id, gebruiker, datum, locatie    --unieke records
from tbl_log
where log_id in (
    select max(log_id) as id        --unieke (max) log-id's
    from tbl_log
    where gebruiker in (
        Select gebruiker        --unieke gebruikers
        From tbl_log
        group by gebruiker
    )
    group by gebruiker
)
order by log_id

De 2e subquery slaat eigenlijk nergens op. Selecteer het maximale log_id waar gebruiker voorkomt in de tabel gebruikers. Dat is altijd. Misschien meer zoiets:

SQL:

select log_id, gebruiker, datum, locatie
from tbl_log
where log_id in (
    select max(log_id) as id
    from tbl_log
    group by gebruiker
)

[ Voor 6% gewijzigd door CoolGamer op 07-11-2007 00:04 . Reden: werkend gemaakt voor MySQL ]

[Verwijderd]

mmm, deze sub qeury lijkt niet te werken. Zou dit komen omdat MySQL geen subquery's ondersteund?

Ik denk niet dat de vraag met een GROUP BY op te lossen is.

[RobIII]

TheCoolGamer schreef op woensdag 07 november 2007 @ 00:00:
[...]

De 2e subquery slaat eigenlijk nergens op.

As said; het is al laat. Je hebt helemaal gelijk, ik 'voelde al aan mijn water' dat er iets niet klopte

Overigens gaan we er nu wel van uit dat een hoger log_id ook een latere datum betreft. Nu zal dat in de praktijk wel zo zijn, maar toch... (Dus als record met log_id = 6 voor het datum veld de waarde 2007-11-01 zou hebben komt die als 'max' eruit, terwijl dat dan log_id = 4 zou moeten zijn)

[ Voor 79% gewijzigd door RobIII op 07-11-2007 00:05 ]

[CoolGamer]

Verwijderd schreef op woensdag 07 november 2007 @ 00:01:
mmm, deze sub qeury lijkt niet te werken. Zou dit komen omdat MySQL geen subquery's ondersteund?

Ik denk niet dat de vraag met een GROUP BY op te lossen is.

Ik heb hem nog even getest met MySQL 5 en hij werkt bij mij wel, dus het kan met een GROUP BY, maar het hoeft niet. Het kan ook op de manier die ik in het eerste bericht postte.

Als het hoogste log_id altijd de hoogste datum bevat zou dit ook kunnen, wat als voordeel heeft dat het ook overweg kan met meerdere keren per dag aanmelden:

SQL:

SELECT *
FROM tbl_log g1
WHERE log_id = (
    SELECT MAX( log_id)
    FROM tbl_log g2
    WHERE g2.gebruiker = g1.gebruiker );

Ik denk dat dit efficiënter is, omdat je een = gebruikt i.p.v. een IN.

[_js_]

Verwijderd schreef op woensdag 07 november 2007 @ 00:01:
mmm, deze sub qeury lijkt niet te werken. Zou dit komen omdat MySQL geen subquery's ondersteund?

Ik denk niet dat de vraag met een GROUP BY op te lossen is.

Dan heb je wel een hele oude versie van MySQL, de versies van de laatste jaren hebben allemaal wel subquerys. Mocht je toch die hele oude MySQL heben dan kun je ipv een subquery ook een join op (nog een keer) de tbl_log doen.

De oplossing hier is "groupwise maximum", deze term is de afgelopen weken wel vaker gevallen, en daar kun je eventueel op zoeken.

Wat je wilt is per gebruiker de hoogste datum, en de daarbij behorende locatie. Dus:

SQL:

SELECT gebruiker,datum,locatie
FROM tbl_log AS log1
WHERE datum = (
  SELECT MAX(datum)
  FROM tbl_log AS log2
  WHERE log1.gebruiker = log2.gebruiker
)

of zonder subquery:

SQL:

SELECT log1.gebruiker,log1.datum,log1.locatie
FROM tbl_log AS log1
INNER JOIN tbl_log AS log2 ON log1.gebruiker = log2.gebruiker
GROUP BY log1.gebruiker,log1.datum,log1.locatie
HAVING log1.datum = MAX(log2.datum)

[RobIII]

_js_ schreef op woensdag 07 november 2007 @ 00:47:

Kijk, daar hebben we meer aan
Je mist enkel nog het id.

SQL:

SELECT log1.log_id, log1.gebruiker,log1.datum,log1.locatie
FROM tbl_log AS log1
INNER JOIN tbl_log AS log2 ON log1.gebruiker = log2.gebruiker
GROUP BY log1.log_id, log1.gebruiker,log1.datum,log1.locatie
HAVING log1.datum = MAX(log2.datum)

En dan ben ik dus toch terug bij mijn originele statement; een aggregate en juist gebruik van group by
_{waarom ik daar zelf eerder vanavond niet op kwam mag Joost weten}

[ Voor 7% gewijzigd door RobIII op 07-11-2007 00:57 ]

[.oisyn]

Volgens mij gaat dat niet werken, omdat je nu ook groepeert op log_id, en die is per definitie uniek wat dus effectief hetzelfde is als helemaal geen groepering

.edit: hmm, dat geldt natuurlijk ook voor de locatie en de datum. Ook dat zijn kolommen die je niet in de group by wilt - je wilt alleen maar groeperen op username.

[ Voor 34% gewijzigd door .oisyn op 07-11-2007 02:00 ]

[RobIII]

Daar zorgt de HAVING voor

[.oisyn]

Ah ja idd ik zie het al, je krijgt idd unieke log1 records terug, en voor elke record heb je een lijstje met alle records voor die user (uit log2) waar je vervolgens de aggregate op toe kan passen, en de HAVING filtert vervolgens die records uit log1 waar de date uit het groepje van log2 niet maximaal is

[Icelus]

Werkt ook op MySQL <4.1

SQL:

   SELECT l1.*
     FROM tbl_log AS l1
LEFT JOIN tbl_log AS l2
       ON l1.gebruiker = l2.gebruiker
      AND l1.datum < l2.datum
    WHERE l2.gebruiker IS NULL
 ORDER BY l1.datum DESC

[Voutloos]

HAVING werkt ook gewoon in de prehistorische mysql versies hoor. Maar goed, deze queries zijn redelijk equivalent.