Ad:

MS SQL Discussion Thread
Unsolved
Views: 16319

Mar 6th, 2007

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

Expand Post »

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

Similar Threads

hard disk space concern for MS SQL in MS SQL

deostroll

Reputation Points: 20

Solved Threads: 0

Newbie Poster

Offline

16 posts
since Jan 2007

Permalink

Mar 6th, 2007

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

what do you mean by isolate?

yerbol

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

28 posts
since Feb 2007

Permalink

Mar 7th, 2007

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

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.

deostroll

Reputation Points: 20

Solved Threads: 0

Newbie Poster

Offline

16 posts
since Jan 2007

Permalink

Mar 7th, 2007

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

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.

yerbol

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

28 posts
since Feb 2007

Permalink

Mar 20th, 2007

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

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.

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

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:

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.

sangramtheroot

Reputation Points: 10

Solved Threads: 1

Newbie Poster

Offline

4 posts
since Mar 2007

Permalink

May 2nd, 2007

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

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.

kmillen

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

6 posts
since Apr 2007

Permalink

May 3rd, 2007

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

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.

sangramtheroot

Reputation Points: 10

Solved Threads: 1

Newbie Poster

Offline

4 posts
since Mar 2007

Permalink

May 3rd, 2007

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

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.

kmillen

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

6 posts
since Apr 2007

Permalink

Aug 24th, 2009

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

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

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.

ashish841987

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Aug 2009

Permalink

Aug 25th, 2009

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

May be this SQL can solve this problem:

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

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

huangzhi

Reputation Points: 10

Solved Threads: 13

Light Poster

Offline

48 posts
since Feb 2008

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: sql delete unique identifier

Next Thread in MS SQL Forum Timeline: MS 2000 Replication error

DaniWeb Message

Cancel Changes

User Name
Password