🚀 go-pugleaf

Your approach is probably best. But let me see if I can come up with a
single query that can do it...using correlated subqueries. Okay, I did, and
it worked with some data of mine, but don't want to reproduce my query and
outputs here.

Try this:

select town, name, salary
from emp e1
where salary >=
    (select min(salary) from emp e2
    where e1.town = e2.town
    and 2 >= (select count(*) from emp e3
            where e3.town = e2.town
             and e3.salary >= e2.salary)
    )
order by town, salary desc, emp


Translation:

Find the employees
whose salary is greater than or equal to
   the minimum of salaries in this town
   out of the salaries where there are no more than 2
   salaries in this town greater than or equal to that salary

Note that if two employees in the town have the same salary, there will be 3
or more rows returned for the town.

I've just recently started to learn the power of subqueries for things like
this. But often they are slow; the approach mentioned below is probably
faster and may be more advisable.

You could also rank the salaries, if you have a column to store the ranks
in.

Very MS SQL Server-specific, a technique I just learned:

alter table emp add rank int
declare @rank int
declare @town varchar(30)
set @rank=0
set @town=''
create clustered index Xemp on emp(town,salary desc)
update emp set rank=@rank=case
    when town<>@town then 1 else @rank+1 end, @town=town
from emp

Then you just select those with ranks of 2 or less.

---

"Robert Djabarov" <Robert.Djabarov@iccs-consulting.com> wrote in message
news:sHPrc.6168$be.809@newsread2.news.pas.earthlink.net...
  You need to create a function that would return TOP 2 or TOP 3, and then
link this function with your CategoryMaster table.

  "ComputerMan" <talktome@btinternet.com> wrote in message
news:c0m8fq$jks$1@hercules.btinternet.com...
  > I am looking for a SQL command that allows me to list for example the
top 2
  > or 3 values for each category. for example I have a table with names,
town,
  > salary. I want the names of the two people who have the highest salaries
in
  > each town.  Imagine the towns are Liverpool, Southport, London. the
result
  > of query should give me two names for each town and the names, towns and
  > salaries must be listed.  The listed names must be those who get the
highest
  > salaries in each town. Looked in every book but not found it. I found a
Top
  > command but didn't give me the correct results. Hope someone can help.
  > Thanks
  >
  >
  >