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.

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.
See if this may work. If you also need to see clients without a
conversation, change the join to a left join.
select client.client_id, Conversations.conv_id
from client
inner join conversations on conversations.client_id = client.client_id
and conversations.conv_id in
(
select top 15 conv_id
from conversations
where client_id = client.client_id
order by conversation_date desc
)
A.Neves wrote:
> 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.
|||Didn't work,
but look here:
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=917505&SiteID=17&mode=1
<Jochen.Markert@.fnf.com> escreveu na mensagem
news:1163189244.535260.305810@.m73g2000cwd.googlegr oups.com...
> See if this may work. If you also need to see clients without a
> conversation, change the join to a left join.
> select client.client_id, Conversations.conv_id
> from client
> inner join conversations on conversations.client_id = client.client_id
> and conversations.conv_id in
> (
> select top 15 conv_id
> from conversations
> where client_id = client.client_id
> order by conversation_date desc
> )
> A.Neves wrote:
>

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.See if this may work. If you also need to see clients without a
conversation, change the join to a left join.
select client.client_id, Conversations.conv_id
from client
inner join conversations on conversations.client_id = client.client_id
and conversations.conv_id in
(
select top 15 conv_id
from conversations
where client_id = client.client_id
order by conversation_date desc
)
A.Neves wrote:
> 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.|||Didn't work,
but look here:
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=917505&SiteID=17&mode=1
<Jochen.Markert@.fnf.com> escreveu na mensagem
news:1163189244.535260.305810@.m73g2000cwd.googlegroups.com...
> See if this may work. If you also need to see clients without a
> conversation, change the join to a left join.
> select client.client_id, Conversations.conv_id
> from client
> inner join conversations on conversations.client_id = client.client_id
> and conversations.conv_id in
> (
> select top 15 conv_id
> from conversations
> where client_id = client.client_id
> order by conversation_date desc
> )
> A.Neves wrote:
>> 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.
>sql

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.See if this may work. If you also need to see clients without a
conversation, change the join to a left join.
select client.client_id, Conversations.conv_id
from client
inner join conversations on conversations.client_id = client.client_id
and conversations.conv_id in
(
select top 15 conv_id
from conversations
where client_id = client.client_id
order by conversation_date desc
)
A.Neves wrote:
> 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_DA
TE
> 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.|||Didn't work,
but look here:
[url]http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=917505&SiteID=17&mode=1[/
url]
<Jochen.Markert@.fnf.com> escreveu na mensagem
news:1163189244.535260.305810@.m73g2000cwd.googlegroups.com...
> See if this may work. If you also need to see clients without a
> conversation, change the join to a left join.
> select client.client_id, Conversations.conv_id
> from client
> inner join conversations on conversations.client_id = client.client_id
> and conversations.conv_id in
> (
> select top 15 conv_id
> from conversations
> where client_id = client.client_id
> order by conversation_date desc
> )
> A.Neves wrote:
>

Get value of MAX(ID) into a variable

Hi All,

Hope someone can help a newbie!

I have the following code:

/* Get MAXID from tblHotels_Web to form the HotelID */SqlCommand cmdGetMaxID =new SqlCommand("Select MAX(HotelID) from tblHotels_Web");int intMaxID;

How do I get the value of HotelID into my intMaxID variable and populate txtID.Text?

Regards,

Brett

see thia example:

static public int AddProductCategory(string newName, string connString){ Int32 newProdID = 0; string sql = "Select MAX(HotelID) from tblHotels_Web"; using (SqlConnection conn = new SqlConnection(connString)) { SqlCommand cmd = new SqlCommand(sql, conn); try { conn.Open(); newProdID = (Int32)cmd.ExecuteScalar(); } catch (Exception ex) { Console.WriteLine(ex.Message); } } return (int)newProdID;}

Get only the number from a string, T-SQL??

I have astring in form "abcdefg 12355 ijklmn"
Now I want get only thenumber 12355 within thestring !!
Is there any function available in T-SQL of Sql server 2K??
Thanks
for any helpI dont think there is any off the shelf function. You would need towrite your own custom function. SQL Server is not really good at stringmanipulations. You could do this very easily using regular expressionsat the front end.
|||

CREATE FUNCTION dbo.fFilterNumeric
(
@.Src nvarchar(255)
)
RETURNS nvarchar(255)
AS
BEGIN
declare @.Res nvarchar(255)
declare @.i int, @.l int, @.c char
select @.i=1, @.l=len(@.Src)
SET @.Res = ''
while @.i<=@.l
begin
set @.c=upper(substring(@.Src,@.i,1))
IF isnumeric(@.c)=1
SET @.Res = @.Res + @.c
set @.i=@.i+1
end

return(@.res)
END

|||The issue I have with IndianScorpion's suggestion is the use of theISNUMERIC function. This function will also return a 1 for somecharacters you'd might not expect, such as a comma (,), dollar sign($), and a period (.).
Try this little script in Query Analyzer to see the ASCII characters between 1 and 254 that SQL Server considers to be numeric:

DECLARE @.myTest char(10), @.a int
SELECT @.a = 1
WHILE @.a < 255
BEGIN
SELECT @.myTest = CHAR(@.a)
IF ISNUMERIC(@.myTest) = 1
PRINT CAST(@.a AS char(3)) + ' -- ' + @.myTest
SELECT @.a = @.a + 1
END

I would do something like this instead:

IF @.c IN ('0','1','2','3','4','5','6','7','8','9')

|||dont like long cycles,
if needs cut just one number without dot (by Ukrainian - крапки),
and number always exists,
try example below, this just example, may this example transform like one select
--
Declare @.X varchar(100)
Select @.X= 'Here is where15234Numbers'
--
Select @.X= SubString(@.X,PATINDEX('%[0-9]%',@.X),Len(@.X))
Select @.X= SubString(@.X,0,PATINDEX('%[^0-9]%',@.X))
--// show result
Select @.X

|||

pmz0178 wrote:

Declare @.X varchar(100)
Select @.X= 'Here is where15234Numbers'
--
Select @.X= SubString(@.X,PATINDEX('%[0-9]%',@.X),Len(@.X))
Select @.X= SubString(@.X,0,PATINDEX('%[^0-9]%',@.X))
--// show result
Select @.X

The limitation of this solution is that a value of @.X like this:

Select @.X= 'Here is where15234Numbers987'

will result in 15234, and not 15234987, as IndianScorpion'swould. This could be a positive thing or a negative thing,depending on the situation.
|||I suggest that TSQL is the wrong tool for the job, at least until you can use the CLR. IMO much better if you can use a client to do this work before it gets to the database, a simple (well when are they) regular expression would sort this kind of problem out.