May 27, 2009 at 9:53 am
i have a table
RecNum, CallID ,SSN, PhoneNumber, Call_Datetime
1 ,123 ,111111111, 9193456789, 12/05/2008
2 ,124 ,111111111, 9193456789, 12/12/2008
3 ,125 ,222222222, 8183456789, 12/06/2008
4 ,126 ,333333333, 7173456789, 12/09/2008
5 ,127 ,333333333, 7173456789, 12/10/2008
i need to query this table and get one record per ssn and get the most recent call_datetime. and i need to see all the fields. notice for recnum 1 and 2, they have the same phone number, i don't care about that... all i need is to display something like this
RecNum, UserID, SSN, PhoneNumber, Call_Datetime
2 ,124 ,111111111, 9193456789, 12/12/2008
3 ,123 ,222222222, 8183456789, 12/06/2008
5 ,127 ,333333333, 7173456789 , 12/10/2008
is that possible?
Thanks in advance
May 27, 2009 at 9:59 am
Look for ROW_NUMBER() function with PARTITION BY clause...
--Ramesh
May 27, 2009 at 10:02 am
you can also try using RANK() function 🙂
May 27, 2009 at 10:10 am
As others have stated, lookup row_number() in books online and also read the article in my signature. It will show you how to post your questions in a way that will give you a tested and validated solution.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 27, 2009 at 10:45 am
would someone help me with this please?
i have been trying but no success.
thanks.
May 27, 2009 at 10:50 am
What have you tried so far? Have you taken the time to read the article I link to in my signature? Have you taken the time to read through books online (help) on the subject row_number()?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 27, 2009 at 10:50 am
You can find a good explanation of ROW_NUMBER at the following url: http://www.sqlservercentral.com/articles/T-SQL/66512/
May 27, 2009 at 10:53 am
Here is some code to play with. Please notice how I have setup the data. this is how you should post your questions instead of relying on others to do it for you.
create table #TempData (
RecNum int,
CallID int,
SSN char(9),
PhoneNumber char(10),
CallDateTime datetime
);
insert into #TempData
select 1 ,123 ,'111111111', '9193456789', '12/05/2008' union all
select 2 ,124 ,'111111111', '9193456789', '12/12/2008' union all
select 3 ,125 ,'222222222', '8183456789', '12/06/2008' union all
select 4 ,126 ,'333333333', '7173456789', '12/09/2008' union all
select 5 ,127 ,'333333333', '7173456789', '12/10/2008'
;
select * from #TempData;
with CallData as (
select
row_number() over (partition by SSN, PhoneNumber order by CallDateTime desc) as RowNum,
RecNum,
CallID,
SSN,
PhoneNumber,
CallDateTime
from
#TempData
)
select
*
from
CallData
where
RowNum = 1;
drop table #TempData;
May 27, 2009 at 11:21 am
I'm sorry for not posting it the right way.
i got it... didn't have to use row_number... here it is in case someone is in a crunch
SELECT T1.*
FROM TableName as T1,
( SELECT SSN, MAX(Call_Datetime) as Call_Datetime
FROM TableName
GROUP BY SSN
) T2
WHERE T1.Call_DateTime = T2.CalL_Datetime AND T1.SSN = T2.SSN
Thanks
May 27, 2009 at 11:28 am
Both solutions work. Thanks for posting yours.
May 27, 2009 at 11:35 am
Thanks Lynn Pettis.
May 27, 2009 at 10:29 pm
Labneh (5/27/2009)
I'm sorry for not posting it the right way.i got it... didn't have to use row_number... here it is in case someone is in a crunch
SELECT T1.*
FROM TableName as T1,
( SELECT SSN, MAX(Call_Datetime) as Call_Datetime
FROM TableName
GROUP BY SSN
) T2
WHERE T1.Call_DateTime = T2.CalL_Datetime AND T1.SSN = T2.SSN
Thanks
What if you have multiple same "Call_Datetime" for same SSN?
--Ramesh
May 28, 2009 at 4:31 am
Ramesh (5/27/2009)
Labneh (5/27/2009)
I'm sorry for not posting it the right way.i got it... didn't have to use row_number... here it is in case someone is in a crunch
SELECT T1.*
FROM TableName as T1,
( SELECT SSN, MAX(Call_Datetime) as Call_Datetime
FROM TableName
GROUP BY SSN
) T2
WHERE T1.Call_DateTime = T2.CalL_Datetime AND T1.SSN = T2.SSN
Thanks
What if you have multiple same "Call_Datetime" for same SSN?
Great question. That shows the strength of Row_number method.
Saurabh Dwivedy
___________________________________________________________
My Blog: http://tinyurl.com/dwivedys
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537
May 28, 2009 at 6:51 am
The row_number() function is better, usually quicker, and more flexible.
I work with Contact Centre data, and do this every day of the week.
June 3, 2009 at 6:42 am
Ramesh,
Thanks man for pointing it out, now i have to redo my query 🙁
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply