Selecting distinct records - Mailing list pgsql-php

From Dave [Hawk-Systems]
Subject Selecting distinct records
Date
Msg-id DBEIKNMKGOBGNDHAAKGNKEFODPAB.dave@hawk-systems.com
Whole thread Raw
Responses Re: Selecting distinct records
List pgsql-php
Have a table that is populated by external radius reporting which contains
    name, ipaddress, sessionid, refid, and others

None of the fields are unique, but the combination of those 4 listed should
reasonably be unique. 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

Dave



pgsql-php by date:

Previous
From: "Francisco Mosse - (Zivals)"
Date:
Subject: problem with PHP
Next
From: "Dave"
Date:
Subject: Re: Selecting distinct records