Solved

Selecting a record by row number in MS SQL

Posted on 2002-06-07
7
416 Views
Last Modified: 2008-03-04
How can I achieve the following in MS SQL ?
a) Selecting a record from the Nth position(row) of a table.
b) selecting a record which is between nth and mth position in a SQL server table.

Assuming table is ordered with a key, which can be varchar. We won;t be knowing the key value, but only the position.

In Oracle we can use rownum. what are options in MS SQL ?

Thanks in advance
Renjith
0
Comment
Question by:renjith
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 7061976
either
* insert all the key values to a table with autonumber and key information, and then select with join.
* using a cursor (very slooooooooooooow)

CHeers
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 7061995
something like this:

select TOP 5 *
from
     (select TOP 25 *
     from StatutImportation
     order by ID ASC) AS AA
order by ID DESC
0
 
LVL 1

Accepted Solution

by:
johan_brohn earned 50 total points
ID: 7062163

To get the N:th row

SELECT TOP 1 Column
FROM (SELECT TOP [N] Column
      FROM Table
      ORDER BY Column ) AS Alias
ORDER BY Column DESC

To get N:th thru M:th row sorted by Column

SELECT *
FROM (SELECT TOP [M - N + 1] Column
      FROM (SELECT TOP M Column
            FROM Table
            ORDER BY Column) AS Alias
      ORDER BY Column DESC) AS Alias
ORDER BY Column
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 7

Expert Comment

by:lozzamoore
ID: 7062165
You can also use a scalar query on the table using the ordered key to produce rownum like so:

select n1.*, (select count(id) from nthtest as n2 where n2.id<=n1.id) as rownum
from nthtest n1


This will probably run slow on big tables though.

Cheers,
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 7128840
Any progress?
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 7297418
This question appears to be abandoned. A question regarding it will be left in the CleanUp
area; if you have any comment about the question, please leave it here.

Unless there is objection or further activity, one of the moderators will be asked to accept the comment
of <emoreau>.

The link to the Community Support area is:
http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt

DO NOT ACCEPT THIS COMMENT AS AN ANSWER.
0
 
LVL 5

Expert Comment

by:Netminder
ID: 7373254
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question