Menu
I'm pulling data from a database and one of the tables contain two columns that together identify a location and another column containing a date of each time it was serviced. Is it possible to write a SQL query such that I get the most recent time that each location was serviced?
So if my raw data looks like this:
category_a category_b date 1 a 1/1/01 1 a 2/2/02 1 b 1/2/01 1 b 2/3/02 2 a 1/3/01 2 a 2/4/02 2 b 1/4/01 2 b 2/5/02 then the query would return this:
category_a category_b date 1 a 2/2/02 1 b 2/3/02 2 a 2/4/02 2 b 2/5/02 This would be easy to do if the database was authored in such a way that the category combinations were stored in a separate table. However, I don't control this database, so I can't make changes to it.
823
To get the last updated record in SQL Server: We can write trigger (which automatically fires) i.e. whenever there is a change (update) that occurs on a row, the “lastupdatedby” column value should get updated by the current timestamp.
MySQL CURDATE() Function The CURDATE() function returns the current date. Note: The date is returned as "YYYY-MM-DD" (string) or as YYYYMMDD (numeric). Note: This function equals the CURRENT_DATE() function.
First, create an aggregate query that has two fields: GroupID and RecordDate. Group by the GroupID field, and choose the "Min" option for the RecordDate, to return the earliest date for each query. Now, create a new query, doing a left-join from your original table to your aggregate query, joining on the GroupID field.
SELECT category_a, category_b, MAX(date) FROM Some_Unnamed_Table GROUP BY category_a, category_b ORDER BY category_a, category_b I certainly don't mind helping people when I can, or I wouldn't be on this site, but did you really search for an answer for this before posting?
165
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
