Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id DB5EC47653D for ; Thu, 21 Nov 2002 13:03:19 -0500 (EST) Received: from web1.nexusinternetsolutions.net (web1.nexusinternetsolutions.net [206.47.131.12]) by postgresql.org (Postfix) with SMTP id 84765476454 for ; Thu, 21 Nov 2002 13:03:12 -0500 (EST) Received: (qmail 5566 invoked from network); 21 Nov 2002 18:03:11 -0000 Received: from unknown (HELO ws1) (24.157.103.51) by web1.nexusinternetsolutions.net with SMTP; 21 Nov 2002 18:03:11 -0000 From: "Dave [Hawk-Systems]" To: Subject: Selecting distinct records Date: Thu, 21 Nov 2002 13:03:10 -0500 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0) X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 Importance: Normal X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200211/82 X-Sequence-Number: 1664 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