We help IT Professionals succeed at work.

SQL statement for latest record

hindersaliva
hindersaliva asked
on
104 Views
Last Modified: 2016-10-19
I'm struggling with this. I have some records like this.

ID, DateTime
-------------------
2225, 01/02/2016
2225, 02/02/2016
2225, 05/02/2016
2226, 01/02/2106
2227, 02/02/2016
2227, 10,02/2016

I want a SELECT statement that will return only the latest record of all the IDs. So in this sample data it will return 3 records, ie. the 3rd, 4th, and 6th one.

Thanks.
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Owner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks guys. I'll try them and report back.
I should have said I'm running it in Access 2016.
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I understood the first one. Good question Vitor.
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
It remembers me those 1001 ways to code the "Hello World" program to try to be as much complicated as you can :)
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
The first solution doesn't return the latest record but a list per ID with its maximum date.
Maximum date is not only the latest date? Or what other interpretation for latest should we have?
And per author requirement I think is really what is pretended:
"So in this sample data it will return 3 records, ie. the 3rd, 4th, and 6th one."
CERTIFIED EXPERT

Commented:
Like I said, what if there are other columns in that table? The query that will return the latest record is this:
SELECT 
	*
FROM 
	myTable t
where
	exists (select * from myTable where ID=t.ID and [DateTime]=(select top 1 [DateTime] from myTable where ID=t.ID order by [DateTime] desc))

Open in new window

ANd that is only if the ID+DateTime combination is unique!
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
So we're entering in the assumption world and not sticking in the question requirement that only has a table with 2 columns.
CERTIFIED EXPERT

Commented:
No, we give an answers for all possible situations but the question was clear: "the last record". There is nothing wrong with that as it was nothing wrong with the first solution either. The asker can judge for himself which one suits his case better.

Author

Commented:
Whoa! Actually you're all helpful.
The first solution showed me the way. I tried it. It worked as I expected.
However, it is true that I wanted to then add 2 more fields. For that I needed the rest of the solution, as I could now see that just adding the fields to the same SELECT doesn't do the job.

The discussion also helped.

What a wonderful forum this is!
Thanks all.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Glad that it worked for you !! Yes more often we require all the columns that corresponds to the maximum value based on the grouping.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.