Is it possible to find the nth row in a non-relational table using sql query?

Question

Say for e.g. I want to isolate the 6th record from an employee table...

# There is nothing that identifies a row in this table.
# The table is finite
# You cannot use any temporary table or anything extra.

Is there a solution for this? If not why do you think so? If yes please state the query.

I am using ms sql server 2000.

I will entertain any other solution regardless of the conditions above.

Answer 1

We can only say about 6th record in context of some ORDERing.
As soon as you determine an order, the problem can be resolved by means of SQL query.

Answer 2

In mySQL or Oracle this would be easy because I believe they both support LIMIT syntax (i.e. "SELECT * FROM table LIMIT 6, 1").

But MSSQL doesn't support LIMIT. And with SQL Server 2000 I don't think you have the ROW_NUMBER() function available.

Without an indentifier, I'm not sure this is possible in MSSQL server 2000. Since they don't support LIMIT, and 2000 doesn't have the ROW_NUMBER(), then I just don't think it can be done.

If you had any column that could be used, even as a pseudo identifier, then you could do this:

SELECT TOP 1 * FROM employee_table WHERE employeeID NOT IN (SELECT TOP 5 employeeID FROM employee_table)

The nested SQL query will return the top 5 employee IDs, and the rest of the original query will return you all the results for employee number six. Substitute whatever employee identifier column you have in your table for employeeID.

The disadvantage of this method is that it's not generic... that is... you have to modify the column depending on what table you're selecting from. Oh well.

I assume a stored procedure is out of the question since you say you cannot use anything extra. Well that's unfortunate because a stored procedure is the only other typical method I can think of to get this done.