Re: query question

From: Michael Austin (maustin_at_firstdbasource.com)
Date: 08/09/04 

Date: Mon, 09 Aug 2004 21:13:53 GMT

Stijn Goris wrote:
> hi all,
>
> 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
> 1704
> 1638
> 1637
> 1623
> 1623
> 1622
> 1621
> 1607
>
> With SELECT DISTINCT this should be (I think)
>
> 1704
> 1638
> 1637
> 1623
> 1622
> 1621
> 1607
> ...
>
> 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.
>
> regards
> Stijn
>
>

the DISTINCT will apply to ALL of the columns listed in the SELECT statement for
example:

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

-- 
Michael Austin.
Consultant - Available.
Donations welcomed. http://www.firstdbasource.com/donations.html
:)

Relevant Pages

  • Re: Clearing all fields with code problem
    ... In a saved query you could use the following as criteria ... The cards are assigned to staff in our building, so the card is either blank, I.e no one has that card or someone has the card and the record is filled in. ... There is only one table apart from several small tables that populate the drop down boxes. ... What code should I enter that will check if there is any thing to delete in my OLE picture field ...
    (microsoft.public.access.gettingstarted)
  • Re: Custom property data type
    ... an error that a date was expected, but with my "Date Picture Taken" ... >> window of Indexing Services and I am able to query by this file property ... >> what to set the datatype to in Property window on Index Server. ...
    (microsoft.public.inetserver.indexserver)
  • Re: Display Form or MsgBox while query is running
    ... You could use the form timer event to animate a picture. ... I can force the label to blink using the "OnTimer" event by simply opening ... The problem is that users have no way of knowing that the query is ...
    (microsoft.public.access.modulesdaovba)
  • Re: One or more of the pictures in this message could not be found
    ... ARGH! ... Sometimes there is a small picture in the signature. ... Tools, Safety Options, Security, uncheck "Block images..." ... blah blah blah...." ...
    (microsoft.public.windows.vista.mail)
  • Re: Adding a parent for existing child row. (typed datasets)
    ... see the query being generated by ado.net for you - chances are ... Specify your own query explicitly that ... A lot of users have not yet selected a picture, ...
    (microsoft.public.dotnet.framework.adonet)