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
