Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2005
    Arnhem, Gld, NL

    Question Unanswered: last 3 reactions to news items (was "Query problem.")

    Hi all,

    On a website i own there is the posibility to post reactions to newsitems.
    On my main page i would like to show the three newsitems who had the latest reactions.

    Here is some information about the database:
    Table news has the followinf fields:

    I aslo have a table News_reactions wich holds the following fields:

    i would like to display the last three newsitems that had a reaction.
    When i do
    select newsid from news_reaction order by datetime DESC limit 3
    I get the three last reactions,.. but it is possible that those are made to the same newsid

    When i do
    select distinct(newsid) from news_reaction order by datetime DESC limit 3
    i get three different newsid's as expected, but the are not in the right order! The seem to apear in some random order, or in order of newsid, but not in order of who had the last reaction.

    Can anyone help me with this?
    I hope my question is clear, please ask for more specific deatils if neccesary

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    select N.Newsid
         , N.Title
         , N.Message
         , N.`Date`
         , R.newsid
         , R.reactionid
         , R.`datetime`
         , R.reaction
      from news as N
      join News_reactions as R
        on R.newsid = N.newsid
       and R.`datetime`
         = ( select max(`datetime`)
               from News_reactions
              where newsid = N.newsid )
        by R.`datetime` desc limit 3 | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2005
    Arnhem, Gld, NL
    Rudy, thanx for answering. But to no avail.
    I tried the query but it gives me an error (1064)

    The error occurs at the subquery, 'select max(date) from ..'
    I have no idea why that happens, because when i try to select the max date from that table it does work.. but in a subquery it seems to trow an error?

    Does anybody else know how to solve this or has any hints/tips?


    (edit,.. i replied, but the board still shows one reaction,.. so testing if this works.)
    Last edited by MoonCrawler; 02-08-06 at 05:06.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts