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

MoonCrawler · #1 (**permalink**) 02-03-06, 08:17

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:
-Newsid
-Title
-Message
-Date

I aslo have a table News_reactions wich holds the following fields:
-newsid
-reactionid
-datetime
-reaction

i would like to display the last three newsitems that had a reaction.
When i do

Code:

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

Code:

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

r937 · #2 (**permalink**) 02-03-06, 16:39

Code:

select N.Newsid
     , N.Title
     , N.Message
     , N.`Date`
     , R.newsid
     , R.reactionid
     , R.`datetime`
     , R.reaction
  from news as N
inner
  join News_reactions as R
    on R.newsid = N.newsid
   and R.`datetime`
     = ( select max(`datetime`)
           from News_reactions
          where newsid = N.newsid )
order
    by R.`datetime` desc limit 3

MoonCrawler · #3 (**permalink**) 02-07-06, 04:46

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?

Thanx

(edit,.. i replied, but the board still shows one reaction,.. so testing if this works.)

Internet Services

Web Development

Digital Marketing

Consumer Tech