0

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.

Val
  • 1,548
  • 1
  • 20
  • 36

3 Answers3

2

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
Community
  • 1
  • 1
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • You mean, TOP will skip rows which have the same timestamp and only give me unique timestamp (distinct) rows? – Val Feb 21 '12 at 10:52
  • Oh, OK, I get it. That's certainly not exactly what I need but at least I'm not loosing any data. I'm trying the PrimaryKey trick... – Val Feb 21 '12 at 11:08
  • OK, seems to be working correctly. I've got 2 messages with the same timestamp and it gave me 1 extra result before. Now gives only as much as I stated in TOP – Val Feb 21 '12 at 11:13
  • I've been using the code for some time today and **it doesn't work properly!** Assuming you have some messages which were added to the database with a timestamp earlier than messages before - if you first sort by time and then by messageId, you will have the exact TOP amount you want but the messages inside this set will be sorted by messageId, not by time. As a result, my app displays the correct amount of messages but they are not listed chronologically. I guess, I will just use the `TOP` amount with `ORDER BY timeReceived DESC` and will cut all the extra messages in the app's code. – Val Feb 24 '12 at 20:10
2

You can try this SQL out:

SELECT top 30 * FROM Mails WHERE timeReceived < #2012-02-01#

user1623521
  • 340
  • 1
  • 16
mehul9595
  • 1,925
  • 7
  • 32
  • 55
  • I don't have a problem with the datetime. I have a problem with the TOP statement :/ – Val Feb 21 '12 at 10:53
1

This should work (unverified):

SELECT top 30 * 
FROM Mails 
WHERE timeReceived < '2012-02-01 12:00:00'
ORDER BY timeReceived desc
juergen d
  • 201,996
  • 37
  • 293
  • 362
  • OK, it works! But I'm surprised because I'm pretty sure I've tried something similar (if not the same) before and it gave me an error :/ – Val Feb 21 '12 at 10:25
  • OK, I also added at the end of the query "ORDER BY timeReceived DESC" because otherwise it gave me messages older then the specified date but not the latest up to that date. – Val Feb 21 '12 at 10:31
  • I made the TOP amount a parameter (`" SELECT TOP ? * FROM Mails WHERE timeReceived < ? ORDER BY timeReceived DESC";`) and it gives me the error I was getting before: **The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect**. The parameter is `getMailsCommand.Parameters.Add("@p1", OleDbType.BigInt).Value = 30;` – Val Feb 21 '12 at 10:37
  • Yes, I've just tried a simple Integer value: 30 - `("@p1", OleDbType.Integer).Value = 30` – Val Feb 21 '12 at 10:43
  • don't forget the `'` around your date: `WHERE timeReceived < '?'` – juergen d Feb 21 '12 at 10:46
  • It does work when I use explicit (no parameters) TOP value and no '' around datetime parameter, but it doesnt work when I change the TOP value to a parameter, both with and without '' around the datetime – Val Feb 21 '12 at 10:49
  • Now I see your problem. You cannot parametrize the top value. See here: http://stackoverflow.com/questions/3355305/n-top-record-selection-based-on-own-sql-statement-in-ms-access – juergen d Feb 21 '12 at 10:54
  • have you tried naming your parameters like `SELECT TOP @p1 * FROM Mails WHERE timeReceived < @p2 ORDER BY ...`? – juergen d Feb 21 '12 at 11:10
  • I doubt that is going to work. Note also that Access selects matches, so top 30 may return more than 30 records, depending on whether or not you have matches. There are work-arounds, but if you must have a parameter, you will need to solve that first. – Fionnuala Feb 21 '12 at 11:13
  • 1
    I've broken the query apart and inserted the parameter in the code: `" SELECT TOP "+ amount.ToString() +" * FROM Mails WHERE timeReceived < ? ORDER BY timeReceived DESC, msgId ASC";` – Val Feb 21 '12 at 11:19
  • I note that you are quoting date/time, but date/time type data in Access takes hash (#) as the delimiter. – Fionnuala Feb 21 '12 at 11:35
  • I'm not quoting datetime. I convert it to string first and then feed to the database as a DBTimeStamp: `getMailsCommand.Parameters.Add("@p1", OleDbType.DBTimeStamp).Value = beforeDate.ToString("yyyy-MM-dd hh:mm:ss");`. I guess the database knows itself that it should add the # for DBTimeStamp parameters – Val Feb 24 '12 at 20:20