Solved

How do I select the latest date infor for the multiple dates database

Posted on 2010-11-16
2
265 Views
Last Modified: 2012-05-10
I forgot how to select the data with the latest date from a large database.  Here is an example of my database:

CustomerID   Timestamp                            Expense .....
abc                 11/14/2010 3:30PM              2000.00
abc                 11/15/2010 9:00AM              1500.00
abc                 11/15/2010 9:15AM               1800.00
0
Comment
Question by:jjxia2001
2 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
Comment Utility
Assuming SQL Server - this can't be Exchange can it?

-- from entire table
select top 1 * from tbl order by timestamp desc

-- latest for each customer
select customerid, timestamp, expense
from
(
select customerid, timestamp, expense,
  rn=row_number() over (partition by customerid order by timestamp desc)
from tbl
) sq
where rn=1
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 250 total points
Comment Utility
if you are doing this in access


select T.*
from tbl1 as T
Inner Join
(select T2.Customerid, Max(T2.Timestamp) AS MaxOfTimestamp
from tbl1 AS T2
group by T2.Customerid) As T3
On T.Timestamp=T3.MaxOfTimestamp and T.Customerid=T3.Customerid
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

Resolve Outlook connectivity issues after moving mailbox to new Exchange 2016 server
Disabling the Directory Sync Service Account in Office 365 will stop directory synchronization from working.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
how to add IIS SMTP to handle application/Scanner relays into office 365.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now