0

I am trying to get the last entry from my database based off the date entered. The sql is not working exactly as I want it. Here is my sql

SELECT TOP 1 * FROM BILLS_HITS ORDER BY DATETIMEADD DESC

Can anyone see anything wrong with it? The sql returns a record with the date - "6/6/2012 7:10:11 AM" and not one that is for 6/10/2012.

JPJedi
  • 1,498
  • 7
  • 32
  • 58

1 Answers1

3

Use a more database-agnostic approach:

SELECT *
FROM
    BILLS_HITS
WHERE
    DATETIMEADD = (SELECT MAX(DATETIMEADD) FROM BILLS_HITS)

The TOP keyword is more specific to MSSQL, while the above query would work in most other DBMSs including MSSQL.

Also, to make the selection fast, you'll likely want to put an index on the column DATETIMEADD

If it doesn't work, make sure the DATETIMEADD column is of a proper date/datetime type and not just a varchar/char/text string.

Zane Bien
  • 22,685
  • 6
  • 45
  • 57
  • Have you done any performance testing on this? I like it, and I think it might out perform an ORDER BY. – Chris Gessler Jun 12 '12 at 01:36
  • Not sure about MSSQL, but at least for MySQL, see comparison [here](http://stackoverflow.com/a/426785/1446794). It is also a more DBMS agnostic approach. The OP will also likely want to put an index on `DATETIMEADD`. – Zane Bien Jun 12 '12 at 01:42