Solved

Selecting a record by row number in MS SQL

Posted on 2002-06-07
7
412 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

739 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