• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 74
  • Last Modified:

SQL statement for latest record

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
hindersaliva
Asked:
hindersaliva
  • 4
  • 3
  • 3
  • +3
6 Solutions
 
Rgonzo1971Commented:
Hi,

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

Open in new window

Regards
0
 
Pawan KumarDatabase ExpertCommented:
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
 
Pawan KumarDatabase ExpertCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Dale FyeCommented:
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
 
hindersalivaAuthor Commented:
Thanks guys. I'll try them and report back.
I should have said I'm running it in Access 2016.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Why so complex solutions when the first one is the simplest one and work perfectly?
0
 
hindersalivaAuthor Commented:
I understood the first one. Good question Vitor.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
It remembers me those 1001 ways to code the "Hello World" program to try to be as much complicated as you can :)
0
 
ZberteocCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
ZberteocCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
So we're entering in the assumption world and not sticking in the question requirement that only has a table with 2 columns.
0
 
ZberteocCommented:
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
 
hindersalivaAuthor 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.
0
 
Pawan KumarDatabase ExpertCommented:
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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 4
  • 3
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now