query question

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
hi all,

I have a table I want to interrogate
CREATE TABLE `tblcomment` (
  `ID` int(11) NOT NULL auto_increment,
  `IDSubonderdeel` int(11) NOT NULL default '0',
  `IDPoster` int(11) default NULL,
  `timestamp` int(11) default NULL,
  `inhoud` text NOT NULL,

Id is the primary key and timestamp contains the date when the comment was
posted. IDSubonderdeel is the ID of the picture comment has been posted too
(more than one comment can be posted to one picture) I want to return a
result containing unique IDSubonderdeel ordered by timestamp (basically what
picture was commented last). SELECT DISTINCT  `tblcomment`.`IDSubonderdeel`
FROM  `tblcomment` ORDER BY timestamp DESC LIMIT 10 should do the trick but
apparently doesn't return the correct timestamp order. When I make comment
to a picture (IDSubonderdeel), that picture isn't always first in the query
row. Somoen knows what I m doing wrong?


Re: query question

*** Stijn Goris escribió/wrote (Mon, 30 Aug 2004 18:48:09 +0200):
Quoted text here. Click to load it

Probably that's not the reason but try enclosing timestamp between inverted

ORDER BY `timestamp`

It's a reserved words in many DBMS.

-+ Álvaro G. Vicario - Burgos, Spain - ICQ 46788716
+- http://www.demogracia.com (la web de humor para mayores de 100 años)
++ «Sonríe, que te vamos a hacer una foto para la esquela»

Re: query question

Quoted text here. Click to load it

Didn't solve the problem :-(


Re: query question

Quoted text here. Click to load it

If you only want the latest timestamp for each IDSubonderdeel:

SELECT MAX(timestamp), ID
FROM tblcomment
GROUP BY IDSubonderdeel

If you want all rows, ordered by IDSubonderdeel and then by timestamp

FROM tblcomment
ORDER BY IDSubonderdeel, timestamp DESC

Tim Van Wassenhove <http://home.mysth.be/~timvw

Site Timeline