Solved

Selecting a record by row number in MS SQL

Posted on 2002-06-07
7
397 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
7 Comments
 
LVL 142

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 69

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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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 69

Expert Comment

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

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now