Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 0A6E04759AF for ; Thu, 21 Nov 2002 20:41:22 -0500 (EST) Received: from seapdc01.PNTS (ip63.c216.blk1.bel.nwlink.com [209.20.216.63]) by postgresql.org (Postfix) with ESMTP id 528DB4758FE for ; Thu, 21 Nov 2002 20:41:21 -0500 (EST) Received: from busbydev (BUSBY-DEV [10.0.0.64]) by seapdc01.PNTS with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id VLKPFZNX; Thu, 21 Nov 2002 17:47:03 -0800 Message-ID: <009c01c291c7$b617aec0$4000000a@busbydev> From: "David Busby" To: "Dave [Hawk-Systems]" , References: Subject: Re: Selecting distinct records Date: Thu, 21 Nov 2002 17:37:25 -0800 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1106 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200211/86 X-Sequence-Number: 1668 Dave, How about a job that runs once that cleans the whole thing? Or: [Warning, pseudo PHPish code] $rs_sid = select distinct (sessionid) from "logs"; while ($s = pg_fetch_object($rs_sid) { $rs_stuff = select * from "logs" where .... and "sessionid" = $s->id for (loop of $rs_stuff) { $total+=$rs_stuff->sessiontime } echo "Session $s->id has $total seconds used"; } This method involves a select for each session (might be slow on millions of records :) ). This might work once or twice but I don't see it as a suitable solution if this has to happen on a daily basis. I know that the latest PostgreSQL does nested selects just fine (I've got one paticular statement that has three nested selects ) and it's performance isn't as bad as you'd think, well at least after the query analyzer has seen it once and it gets cached. /B ----- Original Message ----- From: "Dave [Hawk-Systems]" To: "David Busby" ; Sent: Thursday, November 21, 2002 13:26 Subject: RE: Selecting distinct records > > Try: > >select distinct on (sessionid) sum(sessiontime) from logs > >where name='joeblowuser' and datetime > 1036040400 > >group by sessionid. > > sorry, I wasn't clear... the problem arises when we need to sort the data for > output. postgres demands that the distinct items be first in the ordering > process, which would not allow display by session, name etc... > > the obvious result would be to nest the select statements but can't seem to get > that to work either (working with version 7.0.3) > > eg/ select * from (select distinct on (sessionid) from logs where....) as > tempname order by sessiondate desc > > gives me an error on the second select, so not sure that is a workable solution > either. > > >sum() is an aggerate (sp?) function, that means it munges a field > >(sessiontime) from multiple records into one field in one record. Since you > >are also selecting sessionid (from mulitple records) you need to munge it > >some how, that munge is accomplished via 'group by'. From your previous > >e-mail it seems that (IMHO) the real problem is that duplicates are getting > >inserted via external hardware interaction, this select might be a bandage > >on a wound whose true size isn't known... > > agreed, and that problem has been corrected, but we are dealing with close to a > million records which have these duplicates strewn about within... rather > annoying, looking for a bandaid in teh select to avoid intensive post-select > processing of the output. > > Dave >