[SQL] Distinct op multi-col wil nie werken

[oZy]

SQL:

SELECT 
  DISTINCT (log_module, 
            log_item_id), 
  log_timestamp, 
  log_action_type 
FROM 
  tbl_mod_log 
WHERE 
     (log_module IN ('PDM', 'CMS', 'NWS')) 
 AND (log_action_type IN ('NEW', 'UPD')) 
 ORDER BY log_timestamp DESC

Error; "Line 2: Incorrect syntax near ','"

Je kunt een distinct dus niet op een combinatie van columns uitvoeren... maar dat wil ik wel In deze tabel worden wijzigingen op een cms bijgehouden, nieuwe pagina's, wijzigingen, etc.

Nu wil ik een pagina maken met de laatste 10 wijzigingen, dus had ik ipv de distinct hier boven "TOP 10", en voila, laatste 10 wijzigingen..

...Maar! Het is natuurlijk niet zo netjes dat als een editor 5x een wiijziging doorvoert op pagina X dat er dan ook 5x staat "pagina X is gewijzigd!", dus ik wil 'distincten' op log_module (over welke module gaat het) en log_item_id (welk item id). Iemand een ideetje?

[P_de_B]

hint: GROUP BY, hierover is in Welkom in P&W: FAQ en Beleid *updated: 28-04*veel te vinden

[ Voor 40% gewijzigd door P_de_B op 16-06-2004 16:25 ]

[whoami]

DISTINCT gaat over row - level.

GROUP BY doe je enkel icm aggregaatsfuncties.

[ Voor 45% gewijzigd door whoami op 16-06-2004 16:27 ]

[curry684]

oZy schreef op 16 juni 2004 @ 16:24:
Je kunt een distinct dus niet op een combinatie van columns uitvoeren... maar dat wil ik wel In deze tabel worden wijzigingen op een cms bijgehouden, nieuwe pagina's, wijzigingen, etc.

Ik weet niet wat je als resultaat bij deze query in gedachten had, maar ik kan me er weinig bij voorstellen

Distinct doe je over de hele query of niet.

[oZy]

curry684 schreef op 16 juni 2004 @ 16:39:
[...]
Ik weet niet wat je als resultaat bij deze query in gedachten had, maar ik kan me er weinig bij voorstellen

Distinct doe je over de hele query of niet.

Ok, dat weet ik ook wel maar goed.. DISTINCT zorgt er (volgens mij ) voor dat je geen dubbele records terug krijgt, ik gebruik dit voorbeeld alleen om aan tegeven dat het niet voor de hele row moet gelden maar voor alleen die 2 columns..

In de where clauses zie je wat voor data er in die log_module column kan staan, de action_type geeft aan wat de wijziging is, dus bijv nieuwe pagina (NEW/CMS) of een wijziging in een nieuwsbericht (UPD/NWS). item_id geeft aan welk ID het is van de betreffende module dus een wijziging in nieuwsbericht 7 is iets als UPD/NWS/7.

Wat ik als resultaat wil is dus alleen de laatste record met combinatie NWS/7, en niet eerst NEW/NWS/7 en daarna UPD/NWS/7. beetje duidelijker zo?

Group By heb ik (volgens mij) niets aan.

[curry684]

oZy schreef op 16 juni 2004 @ 16:53:
[...]

Ok, dat weet ik ook wel maar goed.. DISTINCT zorgt er (volgens mij ) voor dat je geen dubbele records terug krijgt, ik gebruik dit voorbeeld alleen om aan tegeven dat het niet voor de hele row moet gelden maar voor alleen die 2 columns..

En dat zeg ik dus dat ik me er weinig bij kan voorstellen

Stel dat log_module over 100 rows maar 5 verschillende waardes bevat. Jij wil dan dus maar 5 rows terugkrijgen. Maar in die 100 rows zitten wel 100 verschillende timestamps, die je dan dus ook in 5 rows wil frotten. Exact welke pak je dan? De hoogste? De laagste?

Wat ik als resultaat wil is dus alleen de laatste record met combinatie NWS/7, en niet eerst NEW/NWS/7 en daarna UPD/NWS/7. beetje duidelijker zo?

Ja, maar het helpt je niet Je probeert data te aggregeren zonder aan te geven waarop geaggregeerd moet worden. En daar heb je aggregate functies voor zoals max(log_timestamp) (hint hint) om dat soort eisen aan te geven.

Group By heb ik (volgens mij) niets aan.

Laat het nu zo zijn dat je geen aggregates mag gebruiken (logisch!) zonder andere kolommen te groupen

[ACM]

oZy schreef op 16 juni 2004 @ 16:53:
Ok, dat weet ik ook wel maar goed.. DISTINCT zorgt er (volgens mij ) voor dat je geen dubbele records terug krijgt, ik gebruik dit voorbeeld alleen om aan tegeven dat het niet voor de hele row moet gelden maar voor alleen die 2 columns..

Dat klopt, maar het hele resultaatset is "een record" Dus jij probeert een distinct over een deel van een record te leggen

In de where clauses zie je wat voor data er in die log_module column kan staan, de action_type geeft aan wat de wijziging is, dus bijv nieuwe pagina (NEW/CMS) of een wijziging in een nieuwsbericht (UPD/NWS). item_id geeft aan welk ID het is van de betreffende module dus een wijziging in nieuwsbericht 7 is iets als UPD/NWS/7.

Wat ik als resultaat wil is dus alleen de laatste record met combinatie NWS/7, en niet eerst NEW/NWS/7 en daarna UPD/NWS/7. beetje duidelijker zo?

Group By heb ik (volgens mij) niets aan.

Group by is inderdaad niet genoeg. Officieel zou je dit met een subquery op kunnen lossen, dus ik hoop niet dat je MySQL gebruikt

Iets in de trant van:

SQL:

SELECT .... FROM ... WHERE 
    (log_item_id, log_timestamp)
    IN
    (SELECT log_item_id, MAX(log_timestamp) FROM ... GROUP BY log_item_id)

[oZy]

ACM schreef op 16 juni 2004 @ 19:09:
[...]

Dat klopt, maar het hele resultaatset is "een record" Dus jij probeert een distinct over een deel van een record te leggen


[...]

Group by is inderdaad niet genoeg. Officieel zou je dit met een subquery op kunnen lossen, dus ik hoop niet dat je MySQL gebruikt

Iets in de trant van:

SQL:

SELECT .... FROM ... WHERE 
    (log_item_id, log_timestamp)
    IN
    (SELECT log_item_id, MAX(log_timestamp) FROM ... GROUP BY log_item_id)

Precies.. dit is ongeveer wat ik nodig heb, ik had al aan een subquery zitten denken maar die Max() is de crux.

Ik gebruik MSSQL 2000 als DBMS, en deze subquery vindtie niet leuk vanwege de comma in "WHERE (log_item_id, log_timestamp).."

[P_de_B]

Dit moet volgens mij doen wat je wilt:

code:

SELECT log_module, log_timestamp, log_action_type
FROM log_items l
WHERE log_timestamp = 
    (SELECT MAX(log_timestamp) from log_items where log_module = l.log_module)

[ Voor 11% gewijzigd door P_de_B op 17-06-2004 15:02 ]

[oZy]

P_de_B schreef op 17 juni 2004 @ 15:00:
Dit moet volgens mij doen wat je wilt:

code:

SELECT log_module, log_timestamp, log_action_type
FROM log_items l
WHERE log_timestamp = 
    (SELECT MAX(log_timestamp) from log_items where log_module = l.log_module)

Niet helemaal, ik krijg nu namelijk maar 1 record terug (omdat er ook maar 1 item is met dat timestamp)

[P_de_B]

oZy schreef op 17 juni 2004 @ 15:41:
[...]

Niet helemaal, ik krijg nu namelijk maar 1 record terug (omdat er ook maar 1 item is met dat timestamp)

Je wilt toch per log_module de laatste bewerking? Dat moet 'ie volgens mij doen.

[ACM]

misschien dat die l.log_module l.log_item_id ofzo moet zijn, maar die query van P_de_B zou wel redelijk moeten kloppen.

Probeer het anders zo:

code:

SELECT .... FROM log_items l, 
    (SELECT log_item_id, MAX(log_timestamp) as log_timestamp FROM ... GROUP BY log_item_id) max_tmstp_id
  WHERE 
    l.log_item_id = max_tmstp_id.log_item_id
AND
l.log_timstamp = max_tmstp_id.log_timestamp

Evt kan je dat ook met een inner join doen.

[ Voor 6% gewijzigd door ACM op 17-06-2004 16:22 ]

[oZy]

P_de_B schreef op 17 juni 2004 @ 16:12:
Je wilt toch per log_module de laatste bewerking? Dat moet 'ie volgens mij doen.

Nee, ik wil het met 1 query (als dat kan).. Dus niet per log_module deze query herhalen.

Iedere query met "WHERE log_timestamp =" lijkt me kansloos omdat je dan hoe dan ook maar 1 record krijgt, en ik wil meerdere records terug, dus per module/id combinatie de laatste wijziging.

[bigbeng]

oZy schreef op 17 juni 2004 @ 17:05:
[...]

Nee, ik wil het met 1 query (als dat kan).. Dus niet per log_module deze query herhalen.

Iedere query met "WHERE log_timestamp =" lijkt me kansloos omdat je dan hoe dan ook maar 1 record krijgt, en ik wil meerdere records terug, dus per module/id combinatie de laatste wijziging.

Dat is precies wat P_de_B bedoelt. Per log_module zou er 1 record terug moeten komen, dus je resultset bestaat uit al je gelogde log_modules met hun laatste logentry timestamp.

Ik heb het idee dat wij jou niet begrijpen en andersom. Toon ons de query eens zoals je die nu hebt gefabriceerd.

[P_de_B]

oZy schreef op 17 juni 2004 @ 17:05:
[...]

Nee, ik wil het met 1 query (als dat kan).. Dus niet per log_module deze query herhalen.

Iedere query met "WHERE log_timestamp =" lijkt me kansloos omdat je dan hoe dan ook maar 1 record krijgt, en ik wil meerdere records terug, dus per module/id combinatie de laatste wijziging.

Je hebt volgens mij de query niet eens geprobeerd?

Er staat nog iets in het WHERE statement, nl. where log_module = l.log_module. Dit betekent dat de query voor elke logmodule uitgevoerd gaat worden.

Als het toch verkeerd gaat moet je even wat voorbeeld data, en de gewenste output bepalen.

[oZy]

bigbeng schreef op 17 juni 2004 @ 17:40:
Ik heb het idee dat wij jou niet begrijpen en andersom. Toon ons de query eens zoals je die nu hebt gefabriceerd.

Zie TS voor mijn query, maar dan zonder de DISTINCT en haakjes op regel 2 en 3.

P_de_B schreef op 18 juni 2004 @ 06:57:
Je hebt volgens mij de query niet eens geprobeerd?

Jawel, maar ik krijg maar 1 result, nl de laatste wijziging, volgens mij omdat alleendie de max(timestamp) voldoet.

Als het toch verkeerd gaat moet je even wat voorbeeld data, en de gewenste output bepalen.

Ok.. meer info;

code:

Table
------------------------------------------------------------------------
log_id | log_module | log_item_id | log_timestamp       | log_action_id
------------------------------------------------------------------------
49       PDM          2             2004-06-11 12:20:04   SEC
50       PDM          3             2004-06-11 12:20:11   SEC
51       PDM          4             2004-06-11 12:20:19   SEC
52       PDM          5             2004-06-11 12:20:26   SEC
53       PDM          6             2004-06-11 12:20:32   SEC
54       PDM          7             2004-06-11 12:20:40   SEC
55       CMS          4             2004-06-11 12:21:07   DEL
56       CMS          6             2004-06-11 12:21:24   NEW
57       CMS          6             2004-06-11 12:21:29   SEC
58       CMS          6             2004-06-11 12:22:16   UPD
59       CMS          1             2004-06-14 16:46:52   NEW
60       CMS          1             2004-06-16 16:00:25   UPD
61       CMS          1             2004-06-16 16:06:42   UPD
------------------------------------------------------------------------

Query
------------------------------------------------------------------------
SELECT
  log_id, log_module, log_item_id, log_timestamp, log_action_type
FROM
  tbl_mod_log
WHERE
      (log_module IN ('PDM', 'CMS', 'NWS')) 
  AND (log_action_type IN ('NEW', 'UPD'))
ORDER BY 
  log_timestamp DESC
------------------------------------------------------------------------


Result
------------------------------------------------------------------------
log_id | log_module | log_item_id | log_timestamp       | log_action_id
------------------------------------------------------------------------
61       CMS          1             16-6-2004 16:06:42    UPD
60       CMS          1             16-6-2004 16:00:25    UPD *
59       CMS          1             14-6-2004 16:46:52    NEW *
58       CMS          6             11-6-2004 12:22:16    UPD
56       CMS          6             11-6-2004 12:21:24    NEW *
47       PDM          7             11-6-2004 12:19:45    NEW
46       PDM          6             11-6-2004 12:19:33    NEW
------------------------------------------------------------------------

Vertaald
------------------------------------------------------------------------
Pagina  1 is gewijzigd  op 16-6-2004 16:06:42
Pagina  1 is gewijzigd  op 16-6-2004 16:00:25 *
Pagina  1 is aangemaakt op 14-6-2004 16:46:52 *
Pagina  6 is gewijzigd  op 11-6-2004 12:22:16
Pagina  6 is gewijzigd  op 11-6-2004 12:21:24 *
Product 7 is aangemaakt op 11-6-2004 12:19:45
product 6 is aangemaakt op 11-6-2004 12:19:33
------------------------------------------------------------------------

De items die ik gemarkeerd heb met een * wil ik dus niet hebben, het moet een lijstje zijn op de startpagina, zodat een gebruiker direct kan zien wat er gewijzigd is. Het is dan natuurlijk niet wenselijk dat er dan 3x hetzelfde item genoemd wordt.

In principe wil ik dus een soort 'group by' op log_item_id maar dat kan dus niet.. is het zo wat duidelijker? Met de query van P_de_B kreeg ik alleen record 61 als result trouwens.

[P_de_B]

Met de query van P_de_B kreeg ik alleen record 61 als result trouwens.

Sorry, maar dat geloof ik niet. Heb je echt mijn hele query gebruikt?

[oZy]

P_de_B schreef op 18 juni 2004 @ 09:59:
Sorry, maar dat geloof ik niet. Heb je echt mijn hele query gebruikt?

Errrm.. dat dacht ik wel ja maar nu ik het nog een keer probeer (nu in query analyser ipv asp) werkt het ineens wel goed _sorry tnx!

[bigbeng]

Ik heb hier even een database gecreeerd met Access die alle gegevens bevat die jij hier in jouw voorbeeld aangaf. De query van P_de_B ziet er na aanpassing aan jouw tabel dan zo uit (let op de haakjes, die zijn essentieel):

code:

SELECT [log_module], [log_timestamp], log_action_id
FROM tbl_mod_log l
WHERE log_timestamp =
    (SELECT MAX(log_timestamp)
     FROM tbl_mod_log
     WHERE log_module = l.log_module
    );

Het resultaat van die query is:

code:

log_module  log_timestamp   log_action_id
PDM 11-6-2004 12:20:40  SEC
CMS 16-6-2004 16:06:42  UPD

Dit lijkt mij toch precies wat je wil, nietwaar?
Als ik trouwens naar jouw query kijk en bedenk wat er mis kan gaan bij het samenvoegen van jouw query met die van P_de_B is dat je jouw where statement hebt toegevoegd aan de subselect, terwijl je hem moet toevoegen aan de buitenste query.

En ik lees nu even dat voorbeeld van jou nog eens door en zie dat je niet alleen de laatste wijziging per module wilt tonen, maar de laatste wijziging per module per logitem. Dit is vrij essentiele informatie!!!!

Nieuwe query wordt dan:

code:

SELECT log_module, log_item_id, MAX(log_timestamp)
FROM tbl_mod_log
WHERE log_action_id IN ('NEW', 'UPD')
GROUP BY log_module, log_item_id;

Hiermee kun je in ieder geval de message zoals in je vertaling geven.

Oh ja, de gegevens van je tabel die je geeft komen niet overeen met je uiteindelijke resultaat (bv log_id 46 kwam niet voor in je tabel, wel in je resultaat)

[oZy]

Jah.. kom er dus net ook achter dat het toch nog niet was zoals ik bedoelde, maar bigbeng was me voor:

bigbeng schreef op 18 juni 2004 @ 11:13:
Dit lijkt mij toch precies wat je wil, nietwaar?
Als ik trouwens naar jouw query kijk en bedenk wat er mis kan gaan bij het samenvoegen van jouw query met die van P_de_B is dat je jouw where statement hebt toegevoegd aan de subselect, terwijl je hem moet toevoegen aan de buitenste query.

En ik lees nu even dat voorbeeld van jou nog eens door en zie dat je niet alleen de laatste wijziging per module wilt tonen, maar de laatste wijziging per module per logitem. Dit is vrij essentiele informatie!!!!

Dat is precies wat er nog ontbrak!

Nieuwe query wordt dan:

code:

SELECT log_module, log_item_id, MAX(log_timestamp)
FROM tbl_mod_log
WHERE log_action_id IN ('NEW', 'UPD')
GROUP BY log_module, log_item_id;

Hiermee kun je in ieder geval de message zoals in je vertaling geven.

Ik ben nu zover:

code:

SELECT     TOP 10 log_module, log_item_id, log_action_type, MAX(log_timestamp) AS [time]
FROM         tbl_mod_log
WHERE     (log_action_type IN ('NEW', 'UPD')) AND (log_module IN ('CMS', 'PDM'))
GROUP BY log_module, log_item_id, log_action_type
ORDER BY [time] DESC

Oh ja, de gegevens van je tabel die je geeft komen niet overeen met je uiteindelijke resultaat (bv log_id 46 kwam niet voor in je tabel, wel in je resultaat)

Jah sorry, heb niet alle records gebruikt! Bedankt voor je hulp so far, maar ik ben er dus toch nog niet

Beetje vreemd.. hij laat nu bijv. CMS 1 NEW niet zien, en maar 1 keer CMS 1 UPD, dat is goed! Maar hij geeft wel CMS 2 NEW én CMS 2 UPD als result en daar wil ik dus alleen de laatste van.. grr..

[ Voor 12% gewijzigd door oZy op 18-06-2004 12:12 ]

[P_de_B]

Zo dan:

code:

SELECT log_module, log_timestamp, log_action_id, log_item_id
FROM log_items l
WHERE log_timestamp = 
    (SELECT MAX(log_timestamp) from log_items 
where log_module = l.log_module and log_item_id = l.log_item_id )

[oZy]

P_de_B schreef op 18 juni 2004 @ 15:49:
Zo dan:

code:

SELECT log_module, log_timestamp, log_action_id, log_item_id
FROM log_items l
WHERE log_timestamp = 
    (SELECT MAX(log_timestamp) from log_items 
where log_module = l.log_module and log_item_id = l.log_item_id )

dat issem, toppie