In my first table, I have the articles, ordered by a unique id, and in the second, I have the visits that those articles receive.
I need to make a SQL syntax that shows me the most readed articles in the past month.
How can I do that? Using Left Outer Join?
by "past month" do you mean march 2010, or do you mean the previous 30 days from today?
yes, it will be a LEFT OUTER JOIN, although i'm guessing that an article that has not been read at all will not be of interest, so you can probably get away with an INNER JOIN
I should join them by inner join then... and I meant 30 days backwards from today.
My current problem is how do I make a count in the join. I know how to make a join, but how do I make a :
count (all from a column that have the same id) as visits
, COUNT(*) AS visits
ON visits.article_id = articles.id
AND visits.visit_date > CURRENT_DATE - INTERVAL '30' DAY
BY visits DESC
Good response! That is what i was searching for... I thank you very much!