Getting the most recent record 2

[LeonelSanchezJr]

I have a table which has data as follows:

Doc# DocDate DocTime DocStatus
123 2001-06-19 00:00:00.000 08:30:01 ABC
123 2001-06-18 00:00:00.000 09:50:49 DEF
123 2001-06-19 00:00:00.000 02:00:01 GHI


In this example, I need to pull the first record because it has the most recent DATE and TIME.

How can I do this? B-)

 

[tlbroadbent]

Try this query.

Select a.*
From Table1 a
Inner Join (Select [Doc#], Max(DocDate+DocTime) As MaxDateTime From Table 1 Group By [Doc#]) As b
On a.[Doc#]=b.[doc#]
And a.DocDate+a.DocTime=b.MaxDateTime Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost

 

[redlam]

I think this would work too:

select top 1
doc_date,
doc_time,
doc_no,
doc_status
from doc_date
order by
doc_date desc,
doc_time desc

I believe TOP evaluates the data in the order specified in the query. This way, you should get the most recent date with the most recent time for that date. Reordering the columns changes your result so test this to be sure...

 

[redlam]

Don't be confused by my table name above... 'doc_date' was the name I gave my table for testing. Its probably confusing since I named one of the columns the same thing...
X-)

 

[tlbroadbent]

The query provided by redlam returns one record from the table - the record with the latest date/time. The query I provided returns the latest record for each doc#. Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost

 

[LeonelSanchezJr]

The only problem with Terry's suggestion is that DocDate is a DATETIME field and DocTime is a CHARACTER field.

How can I work around this?

 

[tlbroadbent]

Convert the date to character data type with the CONVERT function.

Select a.*
From Table1 a
Inner Join (Select [Doc#], Max(convert(char(10),DocDate,102)+DocTime) As MaxDateTime From Table 1 Group By [Doc#]) As b
On a.[Doc#]=b.[doc#]
And convert(char(10),a.DocDate,102)+a.DocTime=b.MaxDateTime Terry

http://members.home.net/tlbroadbent/prog.htm

Neither success nor failure is ever final. -Roger Babson