SQL Statement to Fetch Last Record Ordered By Date and Time
Posted: Sat Jun 12, 2010 9:51 pm
I ahve this SQL Statement to fetch only the Last Record from the specified table:
But it just doesn't work fine as it would otherwise because of the data in the logDate field:
I have intentionally appended a "-" and a "time" and "AM/PM" value to the "date" value. So, I am using a Text data type of field in the table (MS-Access). Now the sorting thing doesn't work as specified by the phrase:
So, what SQL Statement can we devise so as to achive the desired result that the SQL Query on the specified data type and field must return the Last Record?
Please, note that I do have a SrNo or ID field in the table but it is not a numeric or Auto Number field. It is a Text Field and is weird by desgin. I can't rely on it at all. Besides, as it is indexed (since Primary Key Constraint indexes the column by default) the order of the Records are no longer in a chronological order.
So, I have only logDate field to work on to get the record which was last added to the table.
Because of in the logDate field the ORDERing is not doing just what we want.
For example, when I want to fetch the last record from the current table the record for this date is fetched:
even though there are records till 13th June.
Any help?
Please Note: Not only viewing the Last Record is working but also displaying all the records from Latest to Oldest is not working as the column "logDate" is not being properly ORDERed. I can't change the SrNo or ID column's data type to Auto Number / Auto Incremental Type of Field because there are lots of records and dependent tables- Dependencies on this field.
Code: Select all
$sql = "SELECT TOP 1 * FROM tblHDParent ORDER BY logDate DESC;";Code: Select all
10-April-2010 - 05:15 AMCode: Select all
ORDER BY logDate DESCSo, what SQL Statement can we devise so as to achive the desired result that the SQL Query on the specified data type and field must return the Last Record?
Please, note that I do have a SrNo or ID field in the table but it is not a numeric or Auto Number field. It is a Text Field and is weird by desgin. I can't rely on it at all. Besides, as it is indexed (since Primary Key Constraint indexes the column by default) the order of the Records are no longer in a chronological order.
So, I have only logDate field to work on to get the record which was last added to the table.
Because of
Code: Select all
- 05:15 AMFor example, when I want to fetch the last record from the current table the record for this date is fetched:
Code: Select all
9-June-2010 - 07:25 AMAny help?
Please Note: Not only viewing the Last Record is working but also displaying all the records from Latest to Oldest is not working as the column "logDate" is not being properly ORDERed. I can't change the SrNo or ID column's data type to Auto Number / Auto Incremental Type of Field because there are lots of records and dependent tables- Dependencies on this field.