pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Dave [Hawk-Systems] <dave@hawk-systems.com>
To: pgsql-php@postgresql.org
Subject: Selecting distinct records
Date: Thu, 21 Nov 2002 13:03:10 -0500
Message-ID: <DBEIKNMKGOBGNDHAAKGNKEFODPAB.dave@hawk-systems.com> (raw)

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





view thread (5+ messages)  latest in thread

Message-ID: <DBEIKNMKGOBGNDHAAKGNKEFODPAB.dave@hawk-systems.com>
Permalink:  ../DBEIKNMKGOBGNDHAAKGNKEFODPAB.dave@hawk-systems.com/
Also on:    postgresql.org/message-id/DBEIKNMKGOBGNDHAAKGNKEFODPAB.dave@hawk-systems.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-php@postgresql.org
  Cc: dave@hawk-systems.com
  Subject: Re: Selecting distinct records
  In-Reply-To: <DBEIKNMKGOBGNDHAAKGNKEFODPAB.dave@hawk-systems.com>
  References: <DBEIKNMKGOBGNDHAAKGNKEFODPAB.dave@hawk-systems.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox