Finding the nth row (SQL)...debative question

[deostroll]

Is there a way to isolate the nth record from a table using sql queries alone? The table is a non relational one and you cannot use any temporary tables...Do u think there is a possible solution for this in MS SQL Server 2000? Otherwise tell me whatever you know?

PS: I believe this is the right place to post my question...

what do you mean by isolate?

[deostroll]

Is it possible to get that record as the only record in the record-set?

PS: I hope you know what a result-set is...? It is just the records returned after executing a select query.

First I would advise you to have a unique numerical primary key , that's usually the easiest way to return a record with some particular number.

[sangramtheroot]

Here is example sql query for you.

put any integer value in place of N
e.g for first record N=1;
for 2nd record N=2; and so on....

Remember it is not optimized at this time. You may want to optimize it.

Select * From Employee E1 Where
    (N-1) = (Select Count(Distinct(E2.Salary)) From Employee E2 Where
        E2.Salary > E1.Salary)

Sample structure of table is

Create table Employee
(
Eid int,
Name varchar(10),
Salary money
)

Sample Values:

Insert into Employee values (1,'name1',3500);
Insert into Employee values (2,'name2',2500);
Insert into Employee values (3,'name3',2500);
Insert into Employee values (4,'name4',5500);
Insert into Employee values (5,'name5',7500); 
Insert into Employee values (6,'name6',2400);

This query is for Salary criteria. If u want anything else just replace.

:cheesy:

[kmillen]

You shouldn't rely on a specific record being in a specific location in a DBMS (unless you manage the database and its order); no database I know of guarrantees this. Seeking this type of solution tells me that your records are not defined properly to warrant individual selection regardless of their physical location in the database.

Data extraction regardless of physical order IS the power that databases offer.

[sangramtheroot]

Well i must agree to you but improvisation can be done and I just give here is the sample to understand logic behind it...
I must bound to give solution which is generic in nature.
Also enterprise databases are indexed and managed in order to speed up the thing (I agree that this might not be 100% true) .
Sticking to solution I want to say that the whole thing is based on result of the query and clauses used filter result and regardless of how the DBMS stores data.
I must say that you should emphasis on query and logic not on DBMS structure.

[kmillen]

Well, I definitely have no problem with finding the logic and or a generic solution to a problem. Problem solving is why I became a software engineer, but concerning this particular problem, I believe the actions of the DBMS are important.

Attempting to extract a record based on physical location is a deviation from the very spirit of relational database use. And while it is true that indexing may improve performance if done properly, there are many ways to improve record access without physical ordering.

SQL is optimized to extract data based on field values; call me a purist, but a departure from that is a departure from the power of the DBMS.

I did like your solution though sangramtheroot.

[ashish841987]

suppose we have a table employee and inside it there is only 1 col emp_name... we r getting 5th row/emp_name in following example...

this only works in sqlserver 2005...not in 2000

select emp_name from
(
select emp_name, row_number() over(order by getdate()) as r
  from employee
) as t
where r = 5

[huangzhi]

May be this SQL can solve this problem:

create table #tmpT (ABC char(1))

insert #tmpT
  select 'A' union all
  select 'B' union all
  select 'C' union all
  select 'D' union all
  select 'E' union all
  select 'F' union all
  select 'G' union all
  select 'H' union all
  select 'I' union all
  select 'J'

select top 1 ABC
  from (select top 3 ABC from #tmpT order by ABC) X
  order by ABC desc

drop table #tmpT

If you want to get 5th record change 'top 3' become 'top 5'