Re: ResultSet memory usage

On Fri, Jan 11, 2002 at 06:05:40PM +0200, Timo Savola wrote:
> > A possible workaround- If you only need to grab a few rows is there some way
> > to make those rows float to the top using an "order by" & then apply "limit"
> > so you don't have to deal with the huge ResultSet?
>
> I'm using order by, but the point is that I can only make an educated
> guess for the limit parameter. And I can't calculate a "big enough"
> value.
>
> I need to get N first entries with duplicates removed based on one (or
> two) unique column(s). I can't use distinct since I need to select also
> other columns that shouldn't be affected by "distinct". I've thought
> about subselects, etc. but so far the best/cleanest approach I've come
> up with is to use a HashSet for the unique column values on the Java
> end. The down side is that I need to transfer a lot of unnecessary rows
> from to the application, and with PostgreSQL that means all rows.

Hmm, PostgreSQL has a non-SQL-standard extension: 'distinct on (expr)':
I think it might do exactly what you want (n.b. I haven't been following
this whole thread, just say this comment)

test=# select distinct inst from people order by inst;
    inst
-------------
 BCM
 BCM/Rice
 MD Anderson
 Rice
 UH
 UTH
 UTMB
(7 rows)

test=# select distinct inst, lastname from people order by inst;
    inst     |   lastname
-------------+---------------
 BCM         | Beck
 BCM         | Chiu
<snip>
 UTH         | Rodin
 UTMB        | Gorenstein
 UTMB        | Luxon
(74 rows)

test=#  select distinct on (inst) inst, lastname from people order by inst;
    inst     |  lastname
-------------+------------
 BCM         | Beck
 BCM/Rice    | Ma
 MD Anderson | C. MacLeod
 Rice        | Stewart
 UH          | Fox
 UTH         | Brandt
 UTMB        | Gorenstein
(7 rows)

test=# select distinct on (inst) inst, lastname from people order by inst, lastname;
    inst     |  lastname
-------------+------------
 BCM         | Beck
 BCM/Rice    | Ma
 MD Anderson | Aldaz
 Rice        | Bennett
 UH          | Eick
 UTH         | Boerwinkle
 UTMB        | Gorenstein
(7 rows)

test=# select distinct on (inst) inst, lastname from people order by inst, lastname limit 3;
    inst     | lastname
-------------+----------
 BCM         | Beck
 BCM/Rice    | Ma
 MD Anderson | Aldaz
(3 rows)

test=#

Ross