nth height salary from database

CREATE TABLE Employee
(
Id bigint identity not null primary key,
Name nvarchar(255) not null,
Salary DECIMAL not null
)
go

INSERT INTO Employee(Name,Salary) VALUES('Emp1',100001)
go
INSERT INTO Employee(Name,Salary) VALUES('Emp2',100002)
go
INSERT INTO Employee(Name,Salary) VALUES('Emp3',100003)
go
INSERT INTO Employee(Name,Salary) VALUES('Emp4',100004)
go
INSERT INTO Employee(Name,Salary) VALUES('Emp5',100005)
go
INSERT INTO Employee(Name,Salary) VALUES('Emp5-1',100005)
go
INSERT INTO Employee(Name,Salary) VALUES('Emp6',100006)
go
INSERT INTO Employee(Name,Salary) VALUES('Emp6-1',100006)
go
INSERT INTO Employee(Name,Salary) VALUES('Emp7',100007)
go

Executing

SELECT TOP 1 salary
 FROM (
  SELECT DISTINCT TOP 5 salary
  FROM employee
  ORDER BY salary DESC) a
ORDER BY salary

returns the much hoped for salary 100003 - the 5th highest salary.

Removing the 'a' gives us the error: Incorrect syntax near the keyword 'ORDER' since the it's needed by the SQL parser a as a placeholder for the result of the inner select.

http://www.4guysfromrolla.com/webtech/010406-1.shtml[^]

MS SQL Server supports the standard fast variant[^]

Apart from that, putting the salary in the employee table strikes me as a strange design - or is it a temporary table?

I would rather have devided the design into several tables - something like:
Person (Id integer NOT NULL PRIMARY KEY, ...)
Position( Id integer NOT NULL PRIMARY KEY, ...)
Wages(Id integer NOT NULL PRIMARY KEY, Salary DECIMAL ,...)

Employment(Id integer NOT NULL PRIMARY KEY,PersonId integer, PositionId integer, DateTime2 NOT NULL, ThroughTime DateTime2,...)

PositionWage(Id integer NOT NULL PRIMARY KEY, PositionId Integer, WageId integer,FromTime DateTime2 NOT NULL, ThroughTime DateTime2, .... )

This will allow:
a person to hold multiple positions - change jobs.

a position to be filled by multiple employees - and
reflect changes over time - the position stays even
if it's filled by a different person

allow wages to change over time

A "real" solotion needs to be quite a bit more complicated to deal with the "real world" - My point is that having salary as a member of employee will cause your system to loose valuable information over time.

Most things are temporal in nature, your database design should be able to deal with this without loosing/overwriting valuable information.


Regards
Espen Harlinn