Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to select records between given beginning index and ending index

Status
Not open for further replies.

Plato2

Programmer
Dec 6, 2002
192
US
I have I table where Column1 is a unique identifier anddeclared as varchar. I want to select record if I have a starting index and ending index.
I tried this way :
select * from Table1 having count(Column1)>StartIndex and count(Column1)<EndIndex
It gives errors.
How can I solve this problem?
Thanks in advance!
 
There is no such thing as a record number in sql.

Count displays the number of items in a group or the number of items in an entire table that match your where clause.

Records are not kept in sql server tables in the order they were added, unless there is an identity field defined or some other field that has been set up to keep track of the other in which the records where added you cannot retrieve records this way.
 
Can I write a stored procedure to do this task?
 
Assuming you want row number or index as you call it to match the order of column1, here are some suggested methods for getting the result you want.

Method 1: Could become a stored procedure with two parameters, @starttindex and @endindex.

Declare @startindex int, @endindex int
Select @startindex=100, @endindex=199
Create table #temp(Column1 varchar(30), ID int identity)

Set Rowcount @endindex-1

Insert #temp(column1)
Select column1 From Table1
Order By column1

Set rowcount 0

Select *
From Table1 t1
Join #temp t2
On t1.column1=t2.column1
Where t2.ID>StartIndex

Method 2: This assumes the table doesn't already have an identity column and that one can be added. It also assumes that the new values in column1 will always be greater than the current maximum value.
a) Add a clusterd index on column1 if possible. If this index already exists, go to the next step. If it is not possible or not advisable to add a clustered index, skip this method.
b) With the clustered index in place, the data will be sorted in the index order. Add an identity collumn to the table. SQL Server will create incremented values in the order of the data.
c) Add a unique index on the new identity column.
d) Select from the table with a query like the following.

Select *
From table1
Where ID>@startindex and ID<@endindex

Method 3: Not advisable if the table is very large. Has the advantage of not requiring table modifications or creation of a temporary table.

Select *
From table1 t1
Where
(Select count(*)
From table1
Where column1<t1.column1)
Between @startindex And @endindex Terry L. Broadbent - DBA
SQL Server Page:
If you want to get the best answer for your question read faq183-874.
 
hi,
But terry for method 2: what if rows are deleted.Row number would not match identity column value.


Bye
miq
 
Thanks a lot for posting procedures.
I tried method 3 and it was very slow for dt with 10000 records.(like 10 sec or more)
The first method I tried but it doesn't sort data...
and displays more rec. than between index1 and index2
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top