query question

[Stijn Goris]

hi all,

I have a table I want to interrogate
tblcomment:
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,
PRIMARY KEY (`ID`)
) TYPE=MyISAM

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`.`I DSubonderdeel`
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?

regards
Stijn

[Alvaro G. Vicario]

Re: query question

*** Stijn Goris escribió/wrote (Mon, 30 Aug 2004 18:48:09 +0200):[color=blue]
> SELECT DISTINCT `tblcomment`.`I DSubonderdeel`
> FROM `tblcomment` ORDER BY timestamp DESC LIMIT 10[/color]

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

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»
--

[Stijn Goris]

Re: query question


"Alvaro G. Vicario" <kAlvaroNOSPAMT HANKS@terra.es> wrote in message
news:pxamnx1xt3 u.1ep7s4ykt3tte $.dlg@40tude.ne t...[color=blue]
> *** Stijn Goris escribió/wrote (Mon, 30 Aug 2004 18:48:09 +0200):[color=green]
> > SELECT DISTINCT `tblcomment`.`I DSubonderdeel`
> > FROM `tblcomment` ORDER BY timestamp DESC LIMIT 10[/color]
>
> Probably that's not the reason but try enclosing timestamp between[/color]
inverted[color=blue]
> commas:
>
> 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»
> --[/color]

Didn't solve the problem :-(

Stijn

[Tim Van Wassenhove]

Re: query question

In article <41335a45$0$409 0$ba620e4c@news .skynet.be>, Stijn Goris wrote:[color=blue]
> 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,
> PRIMARY KEY (`ID`)
> ) TYPE=MyISAM
>
> 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`.`I DSubonderdeel`
> 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?[/color]

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

SELECT *
FROM tblcomment
ORDER BY IDSubonderdeel, timestamp DESC

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