?
Solved

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

Posted on 2010-11-16
2
Medium Priority
?
311 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 1000 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

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. 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

Want to know how to use Exchange Server Eseutil command? Go through this article as it gives you the know-how.
On September 18, Experts Exchange launched the first installment of the Help Bell, a new feature for Premium Members, Team Accounts, and Qualified Experts. The Help Bell will serve as an additional tool to help teams increase question visibility.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

650 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