Solved

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

Posted on 2010-11-16
2
290 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
ID: 34147804
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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 250 total points
ID: 34147993
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

Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Find out what you should include to make the best professional email signature for your organization.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
how to add IIS SMTP to handle application/Scanner relays into office 365.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

830 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