pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Select subset of rows
5+ messages / 3 participants
[nested] [flat]

* Select subset of rows
@ 2000-08-27 19:50 Paulo Roberto Siqueira <paulo.siqueira@usa.net>
  2000-08-27 20:55 ` Re: Select subset of rows Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2000-08-27 21:08 ` Re: Select subset of rows Stephan Szabo <sszabo@megazone23.bigpanda.com>
  0 siblings, 2 replies; 5+ messages in thread

From: Paulo Roberto Siqueira @ 2000-08-27 19:50 UTC (permalink / raw)
  To: pgsql-sql

	Hi folks,

	I have this table

CREATE TABLE EMPLOYEE (ID_EMP INT4 PRIMARY KEY, NAME VARCHAR(35), SALARY
NUMERIC(5,2));

	I want to select only the employees' names who have the 5 highest salaries.




Paulo Siqueira




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Select subset of rows
  2000-08-27 19:50 Select subset of rows Paulo Roberto Siqueira <paulo.siqueira@usa.net>
@ 2000-08-27 20:55 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
  1 sibling, 0 replies; 5+ messages in thread

From: Stephan Szabo @ 2000-08-27 20:55 UTC (permalink / raw)
  To: Paulo Roberto Siqueira <paulo.siqueira@usa.net>; +Cc: pgsql-sql


On Sun, 27 Aug 2000, Paulo Roberto Siqueira wrote:

> 	Hi folks,
> 
> 	I have this table
> 
> CREATE TABLE EMPLOYEE (ID_EMP INT4 PRIMARY KEY, NAME VARCHAR(35), SALARY
> NUMERIC(5,2));
> 
> 	I want to select only the employees' names who have the 5 highest salaries.

Well, that depends on what you want to do in case of duplicate salaries.

If you don't care about duplicate salaries, and you don't mind the
possibility of non-deterministic responses in some cases:
select name from employee order by salary desc limit 5;

If you want to take the 5 highest salary values and find however many
people have those salaries, I think this works:
begin;
select distinct 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;

If you want to get at least 5 people but don't want to cut off at 5
if the 5th, 6th, etc people are tied, I'm sure there's a better way,
and I probably made at least one mistake in writing this out, but...
begin;
select distinct salary into temp saltemp from employee 
 order by salary desc limit 5;
select salary, count(*) into temp saltemp2 from employee
 where exists(select * from saltemp where saltemp.salary=
 employee.salary) group by salary;
select saltemp2.salary, sum(case when saltemp2_2.salary<=
 saltemp2.salary then 0 else saltemp2_2.count end) into 
 temp saltemp3 from saltemp2,saltemp2 saltemp2_2 group by
 saltemp2.salary;
select name from employee,saltemp3 where employee.salary=
 saltemp3.salary and saltemp3.sum<5 order by employee.salary
 desc;
end;




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Select subset of rows
  2000-08-27 19:50 Select subset of rows Paulo Roberto Siqueira <paulo.siqueira@usa.net>
@ 2000-08-27 21:08 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2000-08-28 03:57   ` Re: Select subset of rows John McKown <jmckown@prodigy.net>
  1 sibling, 1 reply; 5+ messages in thread

From: Stephan Szabo @ 2000-08-27 21:08 UTC (permalink / raw)
  To: Paulo Roberto Siqueira <paulo.siqueira@usa.net>; +Cc: pgsql-sql


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@bigpanda.com

On Sun, 27 Aug 2000, Paulo Roberto Siqueira wrote:

> 	Hi folks,
> 
> 	I have this table
> 
> CREATE TABLE EMPLOYEE (ID_EMP INT4 PRIMARY KEY, NAME VARCHAR(35), SALARY
> NUMERIC(5,2));
> 
> 	I want to select only the employees' names who have the 5 highest salaries.
> 
> 
> 
> 
> Paulo Siqueira
> 




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Select subset of rows
  2000-08-27 19:50 Select subset of rows Paulo Roberto Siqueira <paulo.siqueira@usa.net>
  2000-08-27 21:08 ` Re: Select subset of rows Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2000-08-28 03:57   ` John McKown <jmckown@prodigy.net>
  2000-08-28 04:16     ` Re: Select subset of rows Stephan Szabo <sszabo@megazone23.bigpanda.com>
  0 siblings, 1 reply; 5+ messages in thread

From: John McKown @ 2000-08-28 03:57 UTC (permalink / raw)
  To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: Paulo Roberto Siqueira <paulo.siqueira@usa.net>; pgsql-sql

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@bigpanda.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.

Just a thought,
John




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Select subset of rows
  2000-08-27 19:50 Select subset of rows Paulo Roberto Siqueira <paulo.siqueira@usa.net>
  2000-08-27 21:08 ` Re: Select subset of rows Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2000-08-28 03:57   ` Re: Select subset of rows John McKown <jmckown@prodigy.net>
@ 2000-08-28 04:16     ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Stephan Szabo @ 2000-08-28 04:16 UTC (permalink / raw)
  To: John McKown <jmckown@prodigy.net>; +Cc: Paulo Roberto Siqueira <paulo.siqueira@usa.net>; 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@bigpanda.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.




^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2000-08-28 04:16 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-08-27 19:50 Select subset of rows Paulo Roberto Siqueira <paulo.siqueira@usa.net>
2000-08-27 20:55 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2000-08-27 21:08 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2000-08-28 03:57   ` John McKown <jmckown@prodigy.net>
2000-08-28 04:16     ` Stephan Szabo <sszabo@megazone23.bigpanda.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox