Menu
I have a database table containing the following columns:
id code value datetime timestamp In this table the only unique values reside in id i.e. primary key.
I want to retrieve the last distinct set of records in this table based on the datetime value. For example, let's say below is my table
id code value datetime timestamp 1 1023 23.56 2011-04-05 14:54:52 1234223421 2 1024 23.56 2011-04-05 14:55:52 1234223423 3 1025 23.56 2011-04-05 14:56:52 1234223424 4 1023 23.56 2011-04-05 14:57:52 1234223425 5 1025 23.56 2011-04-05 14:58:52 1234223426 6 1025 23.56 2011-04-05 14:59:52 1234223427 7 1024 23.56 2011-04-05 15:00:12 1234223428 8 1026 23.56 2011-04-05 15:01:14 1234223429 9 1025 23.56 2011-04-05 15:02:22 1234223430 I want to retrieve the records with IDs 4, 7, 8, and 9 i.e. the last set of records with distinct codes (based on datetime value). What I have highlighted is simply an example of what I'm trying to achieve, as this table is going to eventually contain millions of records, and hundreds of individual code values.
What SQL statement can I use to achieve this? I can't seem to get it done with a single SQL statement. My database is MySQL 5.
575
SELECT * FROM (select * from suppliers ORDER BY supplier_name DESC) suppliers2 WHERE rownum <= 3 ORDER BY rownum DESC; Notice that although you want the last 3 records sorted by supplier_name in ascending order, you actually sort the supplier_name in descending order in this solution.
The COUNT DISTINCT function returns the number of unique values in the column or expression, as the following example shows. SELECT COUNT (DISTINCT item_num) FROM items; If the COUNT DISTINCT function encounters NULL values, it ignores them unless every value in the specified column is NULL.
To get the last record, the following is the query. mysql> select *from getLastRecord ORDER BY id DESC LIMIT 1; The following is the output. The above output shows that we have fetched the last record, with Id 4 and Name Carol.
This should work for you.
SELECT * FROM [tableName] WHERE id IN (SELECT MAX(id) FROM [tableName] GROUP BY code) If id is AUTO_INCREMENT, there's no need to worry about the datetime which is far more expensive to compute, as the most recent datetime will also have the highest id.
Update: From a performance standpoint, make sure the id and code columns are indexed when dealing with a large number of records. If id is the primary key, this is built in, but you may need to add a non-clustered index covering code and id.
143
Try this:
SELECT * FROM <YOUR_TABLE> WHERE (code, datetime, timestamp) IN ( SELECT code, MAX(datetime), MAX(timestamp) FROM <YOUR_TABLE> GROUP BY code ) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
