Geting the last inserted row for each CLIENT. How?

Hi,

I have a CLIENTS table with pk CLIENT_ID, and a CONVERSATIONS table where
CONV_ID and CLIENT_ID form the pk, there is another column CONVERSATION_DATE
where the conversation data is registered (and other columns).

Now I need to retrieve, for each client, the last N (for some clients,
eventually, less then N) conversations with one T-SQL statement.

Does anyone knows how to do this? Is it possible with T-SQL only?

Thanks.

Antonio:

Is one of this close to what you are looking for?

Dave

-- --
-- I have used a LEFT JOIN in this case because I think that it
-- is probably important that a client that has not been called
-- be listed.
--
-- I went ahead and added the "recordsToPull" column to provide
-- for the future change when you want to be able to vary the
-- number of conversations returned on a client-by-client
-- basis
-- --
set nocount on

declare @.client table
( client_id integer not null,
recordsToPull integer not null
)

declare @.conversations table
( client_id integer not null,
conv_id integer not null,
conversation_date datetime not null
)

insert into @.client values (1, 4)
insert into @.client values (2, 2)
insert into @.client values (3, 4)
insert into @.client values (4, 4)
--select * from @.client

insert into @.conversations values (1, 1, '1/14/2006')
insert into @.conversations values (1, 2, '10/31/2006')
insert into @.conversations values (1, 3, '3/17/6')

insert into @.conversations values (2, 4, '2/2/6')
insert into @.conversations values (2, 5, '2/2/6')
insert into @.conversations values (2, 6, '2/4/6')
insert into @.conversations values (2, 7, '2/5/6')
insert into @.conversations values (2, 8, '2/8/6')

insert into @.conversations values (3, 9, '7/1/5')
insert into @.conversations values (3, 10, '11/23/5')
insert into @.conversations values (3, 11, '1/17/6')
insert into @.conversations values (3, 12, '4/1/6')
insert into @.conversations values (3, 13, '10/31/6')
--select * from @.conversations

--
-- SQL Server 2005 Version
--
/*
select a.client_id,
a.recordsToPull,
b.conv_id,
b.conversation_date
from @.client a
left join
(
select client_id,
conv_id,
conversation_date,
row_number ()
over ( partition by client_id
order by conversation_date desc, conv_id desc
)
as seq
from @.conversations
) b
on a.client_id = b.client_id
and a.recordsToPull >= b.seq
order by a.client_id,
b.Seq
*/

--
-- SQL Server 2000 Version
--
select a.client_id,
a.recordsToPull,
b.conv_id,
b.conversation_date
from @.client a
left join
(
select x.client_id,
x.conv_id,
x.conversation_date,
count(*) as seq
from @.conversations x
inner join @.conversations y
on x.client_id = y.client_id
and ( x.conversation_date < y.conversation_date or
x.conversation_date = y.conversation_date and
x.conv_id <= y.conv_id
)
group by x.client_id,
x.conversation_date,
x.conv_id
-- order by x.client_id,
-- x.conversation_date desc,
-- x.conv_id desc
) b
on a.client_id = b.client_id
and a.recordsToPull >= b.seq
order by a.client_id,
b.Seq

--
-- Sample Output:
--

-- client_id recordsToPull conv_id conversation_date
-- -- - -- --
-- 1 4 2 2006-10-31 00:00:00.000
-- 1 4 3 2006-03-17 00:00:00.000
-- 1 4 1 2006-01-14 00:00:00.000
-- 2 2 8 2006-02-08 00:00:00.000
-- 2 2 7 2006-02-05 00:00:00.000
-- 3 4 13 2006-10-31 00:00:00.000
-- 3 4 12 2006-04-01 00:00:00.000
-- 3 4 11 2006-01-17 00:00:00.000
-- 3 4 10 2005-11-23 00:00:00.000
-- 4 4 NULL NULL

|||

It would help if you post the version of SQL Server you are using. In SQL Server 2005, you can do below:

select c.*, t.*

from CLIENTS as c

cross apply (

select top(@.n) *

from CONVERSATIONS as cn

where cn.CLIENT_ID = c.CLIENT_ID

order by cn.CONVERSATION_DATE desc

) as t

|||

Thanks

this is exactly what I needed, worked perfectly 2005 version, didn't try 2000, but I'll need it too...

|||

Hi,

I don't know if you tryed it but didn't work for me. Thanks anyway.