473,854 Members | 1,637 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to return a range of rows?

How can a SQL statement be written to return a specified range of
rows? For example:

-- tblContact
-- (
-- SSN char(9),
-- FirstName varchar(50),
-- LastName varchar(50)
-- )
-- This table contains 500 rows.

Select * from tblContact -- Return only rows 5 through 10

Thanks
Jul 20 '05 #1
2 21877

"Briniken" <br******@yahoo .com> wrote in message
news:68******** *************** ***@posting.goo gle.com...
How can a SQL statement be written to return a specified range of
rows? For example:

-- tblContact
-- (
-- SSN char(9),
-- FirstName varchar(50),
-- LastName varchar(50)
-- )
-- This table contains 500 rows.

Select * from tblContact -- Return only rows 5 through 10

Thanks


Data in tables doesn't have any order, so you have to decide how to say
which are the 'first' 10 rows. Assuming that you want rows 5 to 10 when
ordered by LastName, then this is one possible solution:

select top 5 * from
(
select top 10 *
from tblContact
order by LastName asc) dt
order by LastName desc

Alternatively, you can look at the first example in this KB article:

http://support.microsoft.com/default...b;en-us;186133

If you add "having count(*) between 5 and 10" to the query, you should also
get the results you want.

Simon
Jul 20 '05 #2
Hi

There is not equivalent of a row number in SQL Server, therefore you need to
be able to order the values, but something like

SELECT TOP 5 SSN. FirstName, LastName FROM
( SELECT TOP 10 SSN. FirstName, LastName FROM tblContact ORDER BY SSN ASC )
A
ORDER BY SSN DESC

Will give you the rows, but not in order!

Also check out the solution in the following thread
http://groups.google.com/groups?hl=e...ewsposts%2BTOP

John
"Briniken" <br******@yahoo .com> wrote in message
news:68******** *************** ***@posting.goo gle.com...
How can a SQL statement be written to return a specified range of
rows? For example:

-- tblContact
-- (
-- SSN char(9),
-- FirstName varchar(50),
-- LastName varchar(50)
-- )
-- This table contains 500 rows.

Select * from tblContact -- Return only rows 5 through 10

Thanks

Jul 20 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
5408
by: Jorey Bump | last post by:
I can retrieve today's date: mysql> SELECT CURDATE() AS begin; +------------+ | begin | +------------+ | 2005-06-01 | +------------+ 1 row in set (0.00 sec)
4
2478
by: bizt | last post by:
Hi, I want to find an easy way to select only rows within a certain range when I do a select. Im sure I have seen this done with a SELECT statement but cant remember if or how it was done. Can someone help please. If not SELECT, whats the simplest way to do this? I have in the past selected all rows, and using a counter and conditional statement I will only output if within range
0
8067
by: ssims | last post by:
I've got a GridView that's sorted by a stored procedure with ROW_NUMBER: PROCEDURE dbo.GetCalendarsByStatusIDPaged ( @startRowIndex int, @maximumRows int, @statusID int ) AS
0
1420
by: Christopher | last post by:
Hello, I am writing a little application which uses a template excel file and adds some information and then stores the changed file. The problem is, i don't really know how to add these rows - row by row. I can insert a range, but when I insert a range the data that is written below that isn't shifted down like I ment it would/should. Here is some sample code:
1
1091
by: freehockey16 | last post by:
I have a question and would appreciate anyones guidance. I have a list of around 2000 entries each having 7 rows (all entries are numbers). Now I need to find the max value of a certain cell within a range that is limited by other values. So, say I have columns A-G and 2000 rows. I need to find the max value of A in a range where G=X and F=Y. Any help on how to do this? I know how to find the max value of a range, but just don't know how...
17
4589
by: pbd22 | last post by:
Hi. How does one return a range of rows. I know that "Top 5" will return rows 0 - 5 but, how do I get 6 - 10? thanks
6
7583
by: grego9 | last post by:
I am trying to return a value from an access database by looking up a value in excel. This is the code I have copied from another source - but I cannot get it to work - I keep getting a run time error not a valid file name at the cnn.open part I want to look up the value in column A of the excel sheet in an access table called Bank Gtes (guarantees2.mdb database) The matching field is called GTEE_NMBR. If a match is found I simply want to...
1
5636
by: Bonzs | last post by:
I have troule with this macro... geting the used rsnge... Public strName As String, ws As Worksheet Sub Test() Workbooks.Open Filename:= _ "C:\Documents and Settings\User\Desktop\IT Development\Pricer.xls" 'Begins formatting the pricer for generating the EPB... strName = ActiveSheet.Name 'MsgBox strName MsgBox "The Used Range of this Worksheet is: " & GetUsedRange(ws)
6
18719
by: shashi shekhar singh | last post by:
Respected Sir, I am facing problem when i try to deploy my website on iis 7.0 on test page. i have to display some .mht files on iframe in gridview and error looks like below, Server Error in '/' Application. -------------------------------------------------------------------------------- Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index Description: An unhandled exception...
0
9750
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11024
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10672
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9510
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7077
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5937
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4550
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4150
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3182
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.