Camillia
asked on
Getting max record but maybe not use Group BY
This is SQL 2014
I have a table Contacts
I have a table Notes
Notes table can have no records, one or more than one record for a contact.
I just want to get the last added note per contact. For example, if a contact has 4 notes, I want to get the last added note for the contact.
I have lots of columns (in this example, I only show 3). How can I do this without using Group By? if there's no way to do it without using group by...how can I get the last note? (I can create temp tables and then have a final result but I want to avoid doing that.)
I created this example
I have a table Contacts
I have a table Notes
Notes table can have no records, one or more than one record for a contact.
I just want to get the last added note per contact. For example, if a contact has 4 notes, I want to get the last added note for the contact.
I have lots of columns (in this example, I only show 3). How can I do this without using Group By? if there's no way to do it without using group by...how can I get the last note? (I can create temp tables and then have a final result but I want to avoid doing that.)
I created this example
create table #con
(
id int,
firstname varchar(250),
lastname varchar(250)
)
create table #n
(
id int,
conid int,
datecreate datetime,
note1 varchar(250)
)
insert into #con
select 212, 'test1','doe1'
insert into #con
select 208, 'test2','doe2'
insert into #con
select 111, 'test3','doe3'
insert into #notes
select 1,212,getdate(),'first note'
insert into #notes
select 2,212,getdate(),'second note'
insert into #notes
select 3,212,getdate(),'last note - display this' -- display this one
insert into #notes
select 4,208,getdate(),'first note for test2 user' --display this one
-- contact Id 111 has no notes, just show blank it
select c.id,
c.firstname,
c.lastname,
note1
from #con c
left join #notes n on n.conid = c.id
--where n.datecreate = (select max(n.datecreate) from #notes group by conId)
group by c.id,c.firstname,c.lastname, note1,datecreate
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER