Skip site navigation (1) Skip section navigation (2)

Re: Select subset of rows

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: John McKown <jmckown(at)prodigy(dot)net>
Cc: Paulo Roberto Siqueira <paulo(dot)siqueira(at)usa(dot)net>, PGSQL SQL <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Select subset of rows
Date: 2000-08-28 04:16:04
Message-ID: Pine.BSF.4.10.10008272108570.48789-100000@megazone23.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-sql
On Sun, 27 Aug 2000, John McKown wrote:

> On Sun, 27 Aug 2000, Stephan Szabo wrote:
> 
> > 
> > Of course immediately after sending the last message and logging off my
> > ISP I figured out the simpler way for the third one:
> > 
> > begin;
> > select salary into temp saltemp from employee order by salary desc 
> >  limit 5;
> > select name from employee where exists (select * from saltemp where
> >  saltemp.salary=employee.salary);
> > end;
> > 
> > Stephan Szabo
> > sszabo(at)bigpanda(dot)com
> > 
> 
> I wonder if the following might be a bit faster? Or would it be slower?
> 
> select salary into temp saltemp from employee order by salary desc
> limit 5;
> 
> select min(salary) as minsal into test minsal from saltemp;
> 
> select name, salary from employee, minsal
>  where salary >= minsal;
> 
> I don't know the speed difference between my second select and your
> subselect within an EXISTS clause might be.

Not sure.  Probably depends on if the subplan/seq scan for the 
subquery on the one is more or less expensive than the time on the
additional query and processing for the query with the min.

Actually, i guess you might be able to use offset in there to get rid
of the second query too...
if you do like
select salary into temp saltemp from employee order by salary desc
 limit 1 offset 4;
you can probably get the 5th one right out.


In response to

pgsql-sql by date

Next:From: Richard RowellDate: 2000-08-28 07:56:05
Subject: Can I get this all in one query?
Previous:From: John McKownDate: 2000-08-28 03:57:56
Subject: Re: Select subset of rows

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group