sql query

Discussion in 'OT Technology' started by 2000GT, Oct 25, 2005.

  1. 2000GT

    2000GT Active Member

    Joined:
    Jan 21, 2001
    Messages:
    5,771
    Likes Received:
    7
    Location:
    Vancouver
    I have been banging my head against the desk trying to find the best way to do the following:

    Pull a list of uniqe phone numbers from a table, the date and time they were called and the result of the call, in a descending order.

    There are many duplicate phonenumbers in the column titled (phonenum) and I just would like to get a list of records with the last record of each phonenum that was called.

    If this is the table fully populated:

    phonenum | dateofcall | timeofcall | callresultcode
    -------------------------------------------------
    5556667777 | 10/25/2005 | 6:00:00 AM | 14
    5556667777 | 10/24/2005 | 5:00:00 PM | 2
    5556667774 | 10/23/2005 | 10:00:00 AM | 3
    5556667774 | 10/22/2005 | 12:00:00 PM | 19

    I would like my result to be the following:

    phonenum | dateofcall | timeofcall | callresultcode
    -------------------------------------------------
    5556667777 | 10/25/2005 | 6:00:00 AM | 14
    5556667774 | 10/23/2005 | 10:00:00 AM | 3


    Anyone able to help me?
     
  2. kingtoad

    kingtoad OT Supporter

    Joined:
    Sep 2, 2003
    Messages:
    55,923
    Likes Received:
    11
    Location:
    Los Angeles
    SELECT DISTINCT * FROM phonecalls ORDER BY timeofcall DESC;
     
  3. CyberBullets

    CyberBullets I reach to the sky, and call out your name. If I c

    Joined:
    Nov 13, 2001
    Messages:
    11,865
    Likes Received:
    0
    Location:
    BC, Canada/Stockholm, Sweden
    rule of thumb: if you have to use distinct in an sql querey, you have a bad setup for your tables/data and can be normalized.
     
  4. kingtoad

    kingtoad OT Supporter

    Joined:
    Sep 2, 2003
    Messages:
    55,923
    Likes Received:
    11
    Location:
    Los Angeles
    :werd:
     
  5. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    It's not even right anyway...

    First thing you should do i combine the date and time fields together. This gives you a start, need to apply this subquery to get the result code as well.

    SELECT DISTINCT table2.ANI, Max([date] & " " & [time]) AS Expr1
    FROM table2
    GROUP BY table2.ANI;
     
  6. kingtoad

    kingtoad OT Supporter

    Joined:
    Sep 2, 2003
    Messages:
    55,923
    Likes Received:
    11
    Location:
    Los Angeles
    Apparently you're incorrect as well.

    http://forums.offtopic.com/showthread.php?t=2119595
     
  7. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    No I'm not - I said my answer wasn't complete because it was just a subquery to identify the last call - yours isn't even close to doing that.
     
  8. kingtoad

    kingtoad OT Supporter

    Joined:
    Sep 2, 2003
    Messages:
    55,923
    Likes Received:
    11
    Location:
    Los Angeles
    Yours was closer, yes, but regardless, it was still incorrect. Try not to let it bother you that much.

    The correct query:

    select
    st2.phonenum,
    st2.dateofcall,
    st2.timeofcall,
    st2.callresultcode
    from
    (select
    st.phonenum,
    st.dateofcall,
    max(st.timeofcall) as timeofcall
    from
    (select
    phonenum,
    max(dateofcall) as dateofcall
    from
    stupid_table
    group by
    phonenum) t
    inner join stupid_table st on st.phonenum = t.phonenum and st.dateofcall = t.dateofcall
    group by
    st.phonenum,
    st.dateofcall)t2
    inner join stupid_table st2 on st2.phonenum = t2.phonenum and st2.dateofcall = t2.dateofcall and st2.timeofcall = t2.timeofcall
     
  9. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Why would it bother me? I took a different approach to identify the call, that example uses another? It still uses a subquery which is what my point was.
     

Share This Page