Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 318
  • Last Modified:

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

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
jjxia2001
Asked:
jjxia2001
1 Solution
 
cyberkiwiCommented:
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
 
Rey Obrero (Capricorn1)Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now