Re: Selecting distinct records - Mailing list pgsql-php

From Dave [Hawk-Systems]
Subject Re: Selecting distinct records
Date
Msg-id DBEIKNMKGOBGNDHAAKGNEEINDPAB.dave@hawk-systems.com
Whole thread Raw
In response to Re: Selecting distinct records  ("David Busby" <busby@pnts.com>)
List pgsql-php
>    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



pgsql-php by date:

Previous
From: "David Busby"
Date:
Subject: Re: Selecting distinct records
Next
From: "David Busby"
Date:
Subject: Re: Selecting distinct records