Link to home
Start Free TrialLog in
Avatar of Camillia
CamilliaFlag for United States of America

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

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

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Camillia

ASKER

let me try