Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Selecting a record by row number in MS SQL

Posted on 2002-06-07
Medium Priority
425 Views
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 ?

Renjith
0
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

LVL 143

Expert Comment

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

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

johan_brohn earned 200 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

LVL 7

Expert Comment

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

ID: 7128840
Any progress?
0

LVL 70

Expert Comment

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

ID: 7373254
0

## Featured Post

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.
###### Suggested Courses
Course of the Month9 days, 16 hours left to enroll