Post reply

help with distinct please :(

  • May 27, 2009 at 9:53 am

    #210052

    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

    #1000774

    Look for ROW_NUMBER() function with PARTITION BY clause...

    --Ramesh

  • May 27, 2009 at 10:02 am

    #1000778

    you can also try using RANK() function 🙂

  • May 27, 2009 at 10:10 am

    #1000790

    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

    #1000821

    would someone help me with this please?

    i have been trying but no success.

    thanks.

  • May 27, 2009 at 10:50 am

    #1000826

    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

    #1000827

    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

    #1000833

    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

    #1000855

    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

    #1000858

    Both solutions work. Thanks for posting yours.

  • May 27, 2009 at 11:35 am

    #1000862

    Thanks Lynn Pettis.

  • May 27, 2009 at 10:29 pm

    #1001203

    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

    #1001301

    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

    Be Happy!

  • May 28, 2009 at 6:51 am

    #1001361

    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

    #1003937

    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