Val Cool Val Cool - 5 months ago 13
SQL Question

MS Access SQL - select specified amount of rows older than specified datetime

I'm using an MS Access .mdb database in my C# application. The database contains email messages (one row - one message).

I need to get a specified amount of messages which are older than a specified datetime. Let's say, 30 messages before 2012-02-01 12:00:00. I've tried different queries but all of them give me errors. Have tried the TOP, LIMIT and other statements also:

"SELECT * FROM ( SELECT * FROM Mails WHERE (timeReceived < ?) ) LIMIT 0,30";

"SELECT * FROM Mails WHERE (timeReceived = ?) ORDER BY timeReceived DESC LIMIT ?";


etc.

Any hints appriciated.

Answer

You say you've tried TOP clause, but it should work

SELECT TOP 30 * FROM Mails WHERE timeReceived  < '2012-02-01 12:00:00' ORDER BY timeReceived DESC

You must take this into account.

The top directive doesn't return the top n items, as one is easily led to believe. Instead it returns at least n distinct items determined by the ordering of the result.

Edit to clarify:

SELECT TOP 25
FirstName, LastName
FROM Students
WHERE GraduationYear = 2003
ORDER BY GradePointAverage DESC;

http://office.microsoft.com/en-us/access-help/results.aspx?qu=top&ex=1&origin=HA010256402

The TOP predicate does not choose between equal values. In the preceding example, if the twenty-fifth and twenty-sixth highest grade point averages are the same, the query will return 26 records.

So, no, rows with the same timestamp are not skipped. But if the 30th and 31th records(according to the order clause) have the same timestamp, both will be returned and you get 31 records.

If you want to force 30 records to be returned, you need to include the primary key into the Order By to differentiate between tied values:

SELECT TOP 30 * 
FROM Mails 
WHERE timeReceived  < '2012-02-01 12:00:00' 
ORDER BY timeReceived DESC, MailID ASC
Comments