sql gurus....

Discussion in 'OT Technology' started by 2000GT, Jan 13, 2004.

  1. 2000GT

    2000GT Active Member

    Joined:
    Jan 21, 2001
    Messages:
    5,768
    Likes Received:
    7
    Location:
    Vancouver
    I am trying to create a query that pulls ditinct rows from an access table.


    I have data that looks like this

    phonenum | name | dateofcall
    --------------------------------------
    1112224444 John 5/6/2003
    1112224444 John 5/8/2003
    1112224444 John 5/9/2003
    1112224445 Jane 5/2/2003
    1112224445 Jane 5/4/2003
    1112224446 Joe 5/6/2003


    I want to pull one row per phonenum with the latest date...
    The query should return this...

    phonenum | name | dateofcall
    --------------------------------------
    1112224444 John 5/9/2003
    1112224445 Jane 5/4/2003
    1112224446 Joe 5/6/2003


    I am no sql guru and have been working on this for hours... any thoughts?

    Thanks...
     
  2. 2000GT

    2000GT Active Member

    Joined:
    Jan 21, 2001
    Messages:
    5,768
    Likes Received:
    7
    Location:
    Vancouver

    but how do I do that for each phonnum?

    can you spell it out for me... Hours of sql and I cant seem to figure out this simplpe problem.
     
  3. bonerwad

    bonerwad New Member

    Joined:
    Oct 25, 2003
    Messages:
    164
    Likes Received:
    0
    Location:
    I like cheese.
    select phonenum,name,dateofcall from some_table group by phonenum
     
  4. 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
    You have to think of the Grouping function as a statistic on every single field. You can find out the first, the last, the max, the min, the average, the count... etc of fields. You could find out all of those items on ONE field as well. What you can't do is tell SQL to find the last call date for each phone number in the table AND its corresponding name UNLESS you split the two queries up. Even then, the way you end up linking the queries back together may not produce the correct results. So, in the meantime, lets concentrate on finding out the last calldate per phone number.

    Select phonenum, max(dateofcall) from sometable groupby phonenum

    If there was no chance that a call could be made by two different people on the same number, you could add the name in:

    Select phonenum, max(dateofcall), max(name) from sometable groupby phonenum

    Since once you group by phonenum, every name will be identical per number.

    BTW, I would stay away from field names like "name" - when you write code it tends to get confused with the keyword "Name".
     
    Last edited: Jan 14, 2004

Share This Page