user2896120 user2896120 - 1 month ago 10
MySQL Question

Getting last 30 days of records

I have a table called 'Articles' in that table I have 2 columns that will be essential in creating the query I want to create. The first column is the

dateStamp
column which is a datetime type column. The second column is the
Counter
column which is an int(255) column. The
Counter
column technically holds the views for that particular field.

I am trying to create a query that will generate the last 30 days of records. It will then order the records based on most viewed. This query will only pick up 10 records. The current query I have is this:

SELECT *
FROM Articles
WHERE DATEDIFF(day, dateStamp, getdate()) BETWEEN 0 and 30
LIMIT 10
) TOP10
ORDER BY Counter DESC


This query is not displaying any records, but I don't understand what I am doing wrong. Any suggestions?

Answer

The MySQL version of the query would look like this:

SELECT a.*
FROM Articles a
WHERE a.dateStamp >= CURDATE() - interval 30 day 
ORDER BY a.counter DESC
LIMIT 10;

Your query is generating an error. You should look at that error before fixing the query.

The query would look different in SQL Server.