Filter records based on latest timestamp for a partcular DATE

[rp1504]

I have DATETIME stamp in columnB.
I want all the latest rows for a particular date.

ColumnA ColumnB
------------------
NJ 2003-06-30 10:22:02.700
CO 2003-08-04 09:49:13.247
NJ 2003-08-04 09:51:47.667
CO 2003-08-26 11:01:58.743
FL 2004-10-09 11:48:13.610
FL 2004-10-09 11:50:13.610

For e.g from the above data i want output as -

ColumnA ColumnB
------------------
NJ 2003-06-30 10:22:02.700
NJ 2003-08-04 09:51:47.667
CO 2003-08-26 11:01:58.743
FL 2004-10-09 11:50:13.610

Pls. help. Thanks!

 

[SQLSister]

Not understanding why NJ is in your example twice. I fyou really only want the latest datetime this example should help:

Select field1, Max(datefield) from table1 group by field1

Questions about posting. See faq183-874

 

[rp1504]

No, I want the output as shown.

NJ can appear twice in mycase.
Your query would ignore one of my "NJ" records.

I want all the latest records for that day.

 

[SQLSister]

THen why isn;t CO in your result set twice?

Questions about posting. See faq183-874

 

[rp1504]

In the first case
NJ 2003-08-04 09:51:47.667 has the most latest timestamp than "CO". so 1st "CO" record would be ignored. In the 2nd case there is only 1 record for date "08/26"
ie, "CO 2003-08-26 11:01:58.743"

So this would appear in the resultset.