Solved

Selecting a record by row number in MS SQL

Posted on 2002-06-07
7
399 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

867 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

22 Experts available now in Live!

Get 1:1 Help Now