MS SQL

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

Please support our MS SQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Jan 2007

Posts: 16

Reputation: deostroll is an unknown quantity at this point

Solved Threads: 0

deostroll

Offline

Newbie Poster

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

Mar 6th, 2007

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

http://deostroll.spaces.live.com/blog/

•

Join Date: Feb 2007

Posts: 28

Reputation: yerbol is an unknown quantity at this point

Solved Threads: 0

yerbol

Offline

Light Poster

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

Mar 6th, 2007

what do you mean by isolate?

•

Join Date: Jan 2007

Posts: 16

Reputation: deostroll is an unknown quantity at this point

Solved Threads: 0

deostroll

Offline

Newbie Poster

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

Mar 7th, 2007

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.

http://deostroll.spaces.live.com/blog/

•

Join Date: Feb 2007

Posts: 28

Reputation: yerbol is an unknown quantity at this point

Solved Threads: 0

yerbol

Offline

Light Poster

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

Mar 7th, 2007

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.

•

Join Date: Mar 2007

Posts: 4

Reputation: sangramtheroot is an unknown quantity at this point

Solved Threads: 1

sangramtheroot sangramtheroot is offline

Offline

Newbie Poster

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

Mar 20th, 2007

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.

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT * FROM Employee E1 WHERE
    (N-1) = (SELECT Count(DISTINCT(E2.Salary)) FROM Employee E2 WHERE
        E2.Salary > E1.Salary)
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

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
CREATE TABLE Employee
(
Eid int,
Name varchar(10),
Salary money
)
Create table Employee
(
Eid int,
Name varchar(10),
Salary money
)

Sample Values:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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);
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:

Last edited by sangramtheroot; Mar 20th, 2007 at 3:41 am.

•

Join Date: Apr 2007

Posts: 6

Reputation: kmillen is an unknown quantity at this point

Solved Threads: 0

kmillen

Offline

Newbie Poster

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

May 2nd, 2007

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.

•

Join Date: Mar 2007

Posts: 4

Reputation: sangramtheroot is an unknown quantity at this point

Solved Threads: 1

sangramtheroot sangramtheroot is offline

Offline

Newbie Poster

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

May 3rd, 2007

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.

•

Join Date: Apr 2007

Posts: 6

Reputation: kmillen is an unknown quantity at this point

Solved Threads: 0

kmillen

Offline

Newbie Poster

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

May 3rd, 2007

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.

•

Join Date: Aug 2009

Posts: 1

Reputation: ashish841987 is an unknown quantity at this point

Solved Threads: 0

ashish841987 ashish841987 is offline

Offline

Newbie Poster

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

Aug 24th, 2009

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

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT emp_name FROM
(
SELECT emp_name, row_number() over(ORDER BY getdate()) as r
  FROM employee
) as t
WHERE r = 5
select emp_name from
(
select emp_name, row_number() over(order by getdate()) as r
  from employee
) as t
where r = 5

Last edited by peter_budo; Aug 24th, 2009 at 5:42 am. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.

•

Join Date: Feb 2008

Posts: 34

Reputation: huangzhi is an unknown quantity at this point

Solved Threads: 11

huangzhi

Offline

Light Poster

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

#10

Aug 25th, 2009

May be this SQL can solve this problem:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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
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