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.salary
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
--