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 ?";
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;
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