Menu
I'm trying to get the last datetime record from a table that happens to store multiple status. My table looks like so:
+---------+------------------------+-------+ |filename |Dates |Status | +---------+------------------------+-------+ |abc.txt |2012-02-14 12:04:45.397 |Open | |abc.txt |2012-02-14 12:14:20.997 |Closed | |abc.txt |2013-02-14 12:20:59.407 |Open | |dfg.txt |2012-02-14 12:14:20.997 |Closed | |dfg.txt |2013-02-14 12:20:59.407 |Open | +---------+------------------------+-------+ The results should be
+---------+------------------------+-------+ |filename |Dates |Status | +---------+------------------------+-------+ |abc.txt |2013-02-14 12:20:59.407 |Open | |dfg.txt |2013-02-14 12:20:59.407 |Open | +---------+------------------------+-------+ 
671
Since for each row in the subquery result MySQL will need a single fetch based on primary key, the subquery will be put first in the join and the rows will be output in the order of the ids in the subquery (if we omit explicit ORDER BY for the join)
In MySQL, use the DATE() function to retrieve the date from a datetime or timestamp value. This function takes only one argument – either an expression which returns a date/datetime/ timestamp value or the name of a timestamp/datetime column. (In our example, we use a column of the timestamp data type.)
To get yesterday's date, you need to subtract one day from today's date. Use GETDATE() to get today's date (the type is datetime ) and cast it to date . In SQL Server, you can subtract or add any number of days using the DATEADD() function. The DATEADD() function takes three arguments: datepart , number , and date .
If you want one row for each filename, reflecting a specific states and listing the most recent date then this is your friend:
select filename , status , max_date = max( dates ) from some_table t group by filename , status having status = '<your-desired-status-here>' Easy!
187
SELECT * FROM table WHERE Dates IN (SELECT max(Dates) FROM table); 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
