SQL - Find Max ID in notes table

I have the below fields in a table.  

How can I find the Max ID(last entered record) for each record ID. I want to display the 'DateEntered' for the latest notes entry for each RecordID

 [dbo].[Canvass_Notes]

[id]
[RecordID]
[DateEntered]
[datetime]
[Notes]
[UpdatedBy]
EdAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

unknown_routineCommented:
Select RecordId , Max(ID)
From

 [dbo].[Canvass_Notes]

Group by RecordId
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I want to display the 'DateEntered' for the latest notes entry for each RecordID
For that you'll need a subquery
SELECT cn.RecordID, cn_max.max_id, cn.DateEntered
FROM Canvass_Notes cn
JOIN (
   Select RecordId , Max(ID) as max_id
   From Canvass_Notes
   Group by RecordId ) max_cn ON cn.RecordID = max_cn.RecordID AND cn.ID = cn_max.max_id

Open in new window

0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
btw you might need to clarify this question, as the wording is not clear.

>How can I find the Max ID(last entered record) for each record ID.
So .. the max ID value for each RecordID, which is not the same as the max date entered?

>I want to display the 'DateEntered' for the latest notes entry for each RecordID
The latest notes entry is the same as the latest (any column) entry for each RecordID, which is defined in the above line.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

EdAuthor Commented:
Hi Jimhorn

Sorry, Yes That's Exactly what I want to do


>I want to display the 'DateEntered' for the latest notes entry for each RecordID


I've tried the above code but it does not work

I get the error

' The multi part identifier "Cn_Max_Maxid" could not be bound
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Copy-paste your SQL back into this question, to make sure neither of us fat-fingered anything.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
The fat-finger was mine; I was using max_cn and cn_max.  Try this.
CREATE TABLE #Canvass_Notes (
	id int identity(1,1), 
	RecordID int, 
	DateEntered datetime)
	
INSERT INTO #Canvass_Notes (recordid, DateEntered) 
VALUES 
	(1, '2013-03-01'), (1, '2013-03-02'), (1, '2013-03-04'), 
	(2, '2013-05-01'), (2, '2013-05-02'), (2, '2013-05-04'), 
	(3, '2013-04-01'), (3, '2013-04-02'), (3, '2013-04-04') 
	
SELECT cn.RecordID, cn_max.max_id, cn.DateEntered
FROM #Canvass_Notes cn
JOIN (
   Select RecordId , Max(ID) as max_id
   From #Canvass_Notes
   Group by RecordId ) cn_max ON cn.RecordID = cn_max.RecordID AND cn.ID = cn_max.max_id

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
EdAuthor Commented:
Perfect solution. Thanks Jimhorn.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  Good luck with your project.  -Jim
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.