Gary Croxford
asked on
Select Max Time and Max Date from table where time and date are in separate columns
Thank you for looking at my question,
I have a table, Table X, of transactions orderer by date and time. I want to select the most recent transaction.
The transaction date (TransDate) is the date only eg 15/03/2012
The transaction time (TransTime) shows date and time in the format 01/01/1900 12:43:29
Currently I obtain the most recent transaction by first querying Table X for Max(TransDate)
I use a second query to get the latest time for the most recent date
SELECT Max(TransTime) FROM qryTableX_MaxDate INNER JOIN TableX ON qryTableX_MaxDate.MaxOfTRA N_DATE = TableX.TransDate
GROUP BY qryTableX_MaxDate.MaxOfTra nsDate
How do I do this in one query?
I have a table, Table X, of transactions orderer by date and time. I want to select the most recent transaction.
The transaction date (TransDate) is the date only eg 15/03/2012
The transaction time (TransTime) shows date and time in the format 01/01/1900 12:43:29
Currently I obtain the most recent transaction by first querying Table X for Max(TransDate)
I use a second query to get the latest time for the most recent date
SELECT Max(TransTime) FROM qryTableX_MaxDate INNER JOIN TableX ON qryTableX_MaxDate.MaxOfTRA
GROUP BY qryTableX_MaxDate.MaxOfTra
How do I do this in one query?
I'm not sure I understand your question correctly, but maybe something like this:
SELECT Max(TransTime) FROM qryTableX_MaxDate WHERE qryTableX_MaxDate.MaxOfTRA N_DATE = (SELECT MAX(TransDate) FROM TableX)
SELECT Max(TransTime) FROM qryTableX_MaxDate WHERE qryTableX_MaxDate.MaxOfTRA
ASKER
>Cluskitt,
Thanks for responding,
To try and clarify it, I want to get the most recent date and time from TableX where the date and time are in separate columns.
I tried a single query to select max(Date) and Max(Time) from TableX this gave me the maximum date value and the maximum time value but these aren't related to each other.
I need the max(Time) of max(Date) in TableX which I currently get using two queries and am asking can it be achieved using only one.
Thanks for responding,
To try and clarify it, I want to get the most recent date and time from TableX where the date and time are in separate columns.
I tried a single query to select max(Date) and Max(Time) from TableX this gave me the maximum date value and the maximum time value but these aren't related to each other.
I need the max(Time) of max(Date) in TableX which I currently get using two queries and am asking can it be achieved using only one.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Crxfrd,
Did you try my idea? I mocked up a table with separate date and time fields and tried it out to see if it would do the job, it seemed to do the trick, though I appreciate it is somewhat inelegant :o)
Update: Since posting this, I've just seen Mcluskitt's post, that looks a better option. When you said do it in one query, I presumed you meant one select statement (i.e. no subqueries), hence my suggestion.
Did you try my idea? I mocked up a table with separate date and time fields and tried it out to see if it would do the job, it seemed to do the trick, though I appreciate it is somewhat inelegant :o)
Update: Since posting this, I've just seen Mcluskitt's post, that looks a better option. When you said do it in one query, I presumed you meant one select statement (i.e. no subqueries), hence my suggestion.
ASKER
THank you
You could simply do:
SELECT TOP 1
TransDate,
TransTime
FROM
TableX
ORDER BY
TransDate DESC,
TransTime DESC;
Or for a combined date/time:
SELECT TOP 1
TransDate + DateAdd("d", -2, TransTime) As TransDateTime
FROM
TableX
ORDER BY
TransDate DESC,
TransTime DESC;
/gustav
SELECT TOP 1
TransDate,
TransTime
FROM
TableX
ORDER BY
TransDate DESC,
TransTime DESC;
Or for a combined date/time:
SELECT TOP 1
TransDate + DateAdd("d", -2, TransTime) As TransDateTime
FROM
TableX
ORDER BY
TransDate DESC,
TransTime DESC;
/gustav
TransDate 15/03/2012
TransTime 01/01/1900 12:43:29
CombinedDate: CDate(TransDate & " " & Right(TransTime, 8))
and then do a Max on CombinedDate?