Solved

SQL statement for latest record

Posted on 2016-10-12
15
54 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.
0
Comment
Question by:hindersaliva
  • 4
  • 3
  • 3
  • +3
15 Comments
 
LVL 48

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 62 total points
ID: 41839905
Hi,

pls try
SELECT ID, Max(DateTime) AS MaxOfID
FROM myTable
GROUP BY ID;

Open in new window

Regards
0
 
LVL 17

Assisted Solution

by:Pawan Kumar Khowal
Pawan Kumar Khowal earned 62 total points
ID: 41839914
Try..

SELECT Id, DateTime FROM 
(
	SELECT ID, DateTime , ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DateTime DESC) rnk
	FROM myTable
)t
WHERE t.rnk = 1

Open in new window


Hope it helps.
0
 
LVL 17

Assisted Solution

by:Pawan Kumar Khowal
Pawan Kumar Khowal earned 62 total points
ID: 41839923
or try..

SELECT l.ID,t.DateTime FROM 
(
	SELECT DISTINCT ID 
	FROM myTable
)l
CROSS APPLY
(
	SELECT TOP 1 n.DateTime 
	FROM myTable n
	WHERE l.ID = n.Id
	ORDER BY n.DateTime DESC
)t

Open in new window

0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 252 total points
ID: 41839951
Or, if this if you want to run this query in Access, instead of SQL Server

SELECT yourTable.*
FROM yourTable
INNER JOIN (
SELECT ID, Max([DateField]) as MaxDate
FROM yourTable
GROUP BY ID
) as T
ON (yourTable.ID = T.ID)
AND (yourTable.[DateField] = T.MaxDate)
0
 

Author Comment

by:hindersaliva
ID: 41839986
Thanks guys. I'll try them and report back.
I should have said I'm running it in Access 2016.
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 62 total points
ID: 41839992
Why so complex solutions when the first one is the simplest one and work perfectly?
0
 

Author Comment

by:hindersaliva
ID: 41840005
I understood the first one. Good question Vitor.
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41840016
It remembers me those 1001 ways to code the "Hello World" program to try to be as much complicated as you can :)
0
 
LVL 26

Assisted Solution

by:Zberteoc
Zberteoc earned 62 total points
ID: 41840220
The first solution doesn't return the latest record but a list per ID with its maximum date. It could be that in this case it is the same but not necessarily. What if there are other columns in that table and only the 2 were given for exemplification?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41840228
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."
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41840234
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!
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41840239
So we're entering in the assumption world and not sticking in the question requirement that only has a table with 2 columns.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41840296
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.
0
 

Author Comment

by:hindersaliva
ID: 41840550
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.
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41840553
Glad that it worked for you !! Yes more often we require all the columns that corresponds to the maximum value based on the grouping.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

705 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

19 Experts available now in Live!

Get 1:1 Help Now