MAX Of TOP In Derived Table Behaving Strange

Hi,

I am working for my client on SQL 2005 Enterprise & observed a strange behaviour. Consider a table(EMPTABLE) as below:









PK, INT

VARCHAR


EMPID

EMPNAME


1

A


2

B


3

C


4

D


5

E


6

F


7

G


8

H


9

I


10

J


Now, I wanted the 5th highest EMPID from 2 from the table. Now, we know that the SQL

SELECT TOP 6 EMPID FROM EMPTABLE WHERE EMPID >=2 should (from all other possibilities) return me the desired value as the last row of the output table. I tried to get this in a derived table & get the MAX value i.e.

SELECT MAX(EMPID) FROM
(
SELECT TOP 6 EMPID FROM EMPTABLE WHERE EMPID >=2
) der

But this returns me 10, when I would've thought 7 was the expected answer

I remodified my query as mentioned below to strangely get 7 as the result...
SELECT MAX(EMPID) FROM
(
SELECT TOP 6 EMPID FROM EMPTABLE WHERE EMPID >=2 ORDER BY EMPID ASC
) der


How did the ORDER BY hep it???

Also, if I use
SELECT MAX(EMPID) FROM
(
SELECT TOP 6 EMPID FROM EMPTABLE WHERE EMPID >2
) der


I get the same result without having to specify the ORDER BY in my derived TABLE...

I tried looking for similar threads. But nothing could explain this to me...

If anyone knows what I am not able to understand, please let me know.