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

Re: Select subset of rows

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: Paulo Roberto Siqueira <paulo(dot)siqueira(at)usa(dot)net>
Cc: PGSQL SQL <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Select subset of rows
Date: 2000-08-27 20:55:30
Message-ID: Pine.BSF.4.10.10008271332250.47595-100000@megazone23.bigpanda.com (view raw or flat)
Thread:
Lists: 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;


In response to

pgsql-sql by date

Next:From: Stephan SzaboDate: 2000-08-27 21:08:27
Subject: Re: Select subset of rows
Previous:From: Paulo Roberto SiqueiraDate: 2000-08-27 19:50:04
Subject: Select subset of rows

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