Do you have a question? Post it now! No Registration Necessary. Now with pictures!
- Posted on
- query question
- Stijn Goris
August 9, 2004, 8:29 am
rate this thread
I have a system where users can add comment to a picture (Gallery system).
When the users opens the webgalley he first sees the last comments made by
the users (the last 8). I could use a simple SELECT and ORDER BY on the
timestamp but when users would make 8 comments to a picture all the last
comments would be taken by that sole picture. My system should be somewhat
more intelligent and not give the last comments but give the last commented
pictures. So when 8 comments are made to one picture it would only appear
once in the last comment.
Via some INNER JOINS I query the pictureID ORDERED BY the timestamp (when
the comment was made). I though I 'd simply change the SELECT into a SELECT
DISTINCT but this doen't seem to work. I get a totally different result.
When I use SELECT I get
With SELECT DISTINCT this should be (I think)
Instead I get a totally different result. Is this possible? If so, I
probably misunderstand the DISTINCT feature. I hope someone can help me out.
Re: query question
I realy think it would be easyer to solve your problem when you would
post the results of the query with DISTINCT in it (and maybe also the
Besides that, I think it's better for performance if you change a column
pictures.last_comment every time someone posts a comment. The query will
be much easier (just SELECT ... FROM pictures ORDER BY last_comment DESC).
If you realy don't want this, you should check out OUTER JOINS.
Re: query question
the DISTINCT will apply to ALL of the columns listed in the SELECT statement for
SELECT DISTINCT A.ID , B.comment, B.user from ....
applies to the entire ID+comment+user and since each is unique, they are
distinct - even if you have the same user.
Since MySQL 4.xx does not have derrived tables ie:
select x,y,z from (select a,b,c from x where blah blah)
you sort of have to do this in 2 steps
select distinct user from comments limit 8 ;
select b.comment, b.comment_date from a a, b b where a.id = b.id and b.user in
(user1,user2,user3...) order by comment_date desc;
since I don't have your table layout or the actual query, the exact syntax will
be yours to work out...
Consultant - Available.
Donations welcomed. http://www.firstdbasource.com/donations.html
- » PHP: "an unknown filter was not added" error message
- — Previous thread in » PHP Scripting Forum