Results 1 to 2 of 2

Thread: Query Help

  1. #1
    Join Date
    Apr 2004
    Location
    Inside Intel
    Posts
    165

    Talking Unanswered: Query Help

    Have a table that is populated by external radius reporting which contains
    name, ipaddress, sessionid, refid, and others

    There is a certain type of interaction with external hardware that results in multple entries into the table, resulting in multiple duplicate entries.

    What we need to to is build a select that omits any duplicate records from the
    output, but I can't get a distinct to work given the way we need the data
    sorted.

    For example,
    Name IPaddress sessionid refid
    User1 201.201 1234 5678 first user
    User2 201.201 1235 5679 same ip as first user
    User3 201.202 3234 5670 same sessid as first user
    User4 201.203 3236 5678 same refid as first user
    User1 201.202 4234 5678 first user new entry
    User1 201.202 4234 5678 DUPLICATE
    User1 201.202 4234 5678 DUPLICATE
    User2 201.203 1234 5671 same ip as user 4
    User3 201.204 2234 5672 unique

    if we do distinct on sessionid then put a where clause for the user=User1 we end up missing some of user1's entries where another users identical sessionid
    appears first.

    What I am looking to do is
    - grab every record for user
    - remove any records that have identical ipaddress+sessionid+refid
    ie: turn
    user1 201.102 1234 5678
    user1 201.102 1234 5678
    user1 201.102 1234 5678
    into
    user1 201.102 1234 5678
    - then sort the results by date_time or something else

    Just can't get it to do all those things at the same time. Any thoughts, or am
    I not making sense?

    Thanks in advance,

    Newbie

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    DISTINCT Works on the whole row...you want DISTINCT

    ipaddress+sessionid+refid


    Now take a look at the other columns..they all have different data..what do you want to do about that? Take the MIN Value, MAX Value?

    You need to do something with them...like

    SELECT Col1, Col2, Col3, MAX(Col4), MAX(Col5), SUM(Col6)
    FROM myTable99
    GROUP BY Col1, Col2, Col3

    If not, then what would you propose..
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •