SQL Query - Please Help!!

[ComputerMan]

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

[David Portas]

Re: SQL Query - Please Help!!

The problem with your specification is what to do when there isn't a
well-defined "top 2" or "top 3" or "top N" for any particular town. See
London in the example below. Which employees are the two highest paid in
London?

CREATE TABLE Employees (empid INTEGER PRIMARY KEY, empname VARCHAR(25) NOT
NULL UNIQUE, town VARCHAR(25) NOT NULL, salary NUMERIC(10,0) NOT NULL)

INSERT INTO Employees VALUES (1, 'Nancy Davolio', 'Liverpool', 54670)
INSERT INTO Employees VALUES (2, 'Andrew Fuller', 'Liverpool', 34570)
INSERT INTO Employees VALUES (3, 'Janet Leverling', 'Southport', 33550)
INSERT INTO Employees VALUES (4, 'Margaret Peacock', 'London', 51760)
INSERT INTO Employees VALUES (5, 'Steven Buchanan', 'Southport', 34530)
INSERT INTO Employees VALUES (6, 'Michael Suyama', 'London', 46500)
INSERT INTO Employees VALUES (7, 'Robert King', 'London', 46500)
INSERT INTO Employees VALUES (8, 'Laura Callahan', 'Liverpool', 23440)
INSERT INTO Employees VALUES (9, 'Anne Dodsworth', 'Southport', 45200)

Assuming you want to include all tied values in the result, giving *not less
than* two rows per town:

SELECT E1.empid, E1.empname, E1.town, E1.salary
FROM Employees AS E1
LEFT JOIN Employees AS E2
ON E1.town = E2.town AND E1.salary<E2.sa lary
GROUP BY E1.empid, E1.empname, E1.town, E1.salary
HAVING COUNT(E2.empid) <2
ORDER BY E1.town, E1.salary DESC

--
David Portas
SQL Server MVP
--