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