ms-sql select subset of select

Hi!

is this possible in ms-sql (it is possible in mysql using LIMIT):
i do a select and get a return set of 2000 rows.
But I only want to get the rows 100-300!
Is there a parameter like:
select a,b,c from xy where a=1 LIMIT 100,200

thanks
michael
mwetzerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustavo Perez BuenrostroCommented:
Hi michael,

In SQL server there is no LIMIT Functionality as in MySql.However, you can limit the number of rows returned in the result set using any of these alternatives:

- ROWCOUNT option of SET statement.
- TOP argument of SELECT statement.
- IDENTITY function.

PD: Post your table’s structure and more information in detail if you need a better advice.
0
highmarksCommented:
gpbuenrostro is right
Or else u can add one serialno field in your existing table structure and write your selec statement as
select a,b,c from xy where a=1 and serialno>=100 and serialno <=200
if u use between clause it will be bit slow than above clause
0
mwetzerAuthor Commented:
gpbuenrostro:
this sounds good!
imagine the table:
int a, int b, int c
primary key a
a is ascending, but rows can be deleted!
so, if a select yields 2000 rows, and I only want the 100 to 199th row, I can't use "where a>=100 and a<=199" (or beetween), because rows with a=102 and a=198 have been deleted, so I would only get 98 instead of 100 rows ...

how to use this (to get the result rows 100-199)?
select a,b,c,... from xxx,... where .... order by a ????????

(in mysql it is:
select a,b,c,... from xxx,... where .... order by a LIMIT 100,100
)

thanks
witty
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Gustavo Perez BuenrostroCommented:
You can create a stored procedure that retrieves n rows from your table taking into account a specific column value. Check next code and let me know your opinion.

/*
Create a test table and populate it with data
*/

create table test_table (col_id int,col_data char(1))

declare @liId int
select @liId=1
while @liId<201
  begin
    insert test_table
    select @liId,(select char(cast(rand()*25 as int)+65))
    select @liId=@liId+1
  end


/*
Create ths SP
*/

create proc spRetrieveData
 @liRowsReturned int=1 output -- specifies how many rows are returned
,@liLastRowId int=0 output -- returns last row id of the record set
as
begin
  select @liLastRowId=isnull(@liLastRowId,0)
        ,@liRowsReturned=isnull(@liRowsReturned,1)
  set rowcount @liRowsReturned
  select *
    from test_table
   where col_id>@liLastRowId

  select @liLastRowId=col_id
    from test_table
   where col_id>@liLastRowId

  select @liRowsReturned=@@rowcount
  set rowcount 0
end

/*
You must run the SP as follows:
*/


declare
 @liRowsReturned int
,@liLastRowId int

select
 @liRowsReturned=50
,@liLastRowId=49

exec spRetrieveData
     @liRowsReturned output
    ,@liLastRowId output

select
 @liRowsReturned
,@liLastRowId
0
FlubbadubCommented:
You could do your select into a temporary table with a primary key Identity 1,1 and then use the >= 100 <= Method outlined???
0
wittyCommented:
gpbuenrostro,
thanks for your "program" - but I think, I will not b e able to use it, because I have to write the select at home, and don't have access to a sql-server! And it has to run immediatly with the sql-server!
but what about your first hint - I think this is my solution!
I'm not familiar with these:
- ROWCOUNT option of SET statement.
- TOP argument of SELECT statement.
- IDENTITY function.
can't I use these in a short select (only in "server side selects")

another solution would be: imagine a table "alpha" with only ONE column "char" and the values "A-Z" (26 rows)
can I generate a output like (where i is not a saved value, but a kind of result-row-id):
i  char
--------
1 A
2 B
3 C
.....
26 Z

then I could use this i (where i<=200 and i>100)


Flubbadub,
thanks - this is a good idea - this will work ever, but I need the select for a www-interface: the select results in about 2000-20000 rows and only 50 to 100 should be shown each time, then you have a next and back button,...
you rarely should need the back and next buttons, but...
and so I think it's not performent enough!

thank you
Michael
0
Gustavo Perez BuenrostroCommented:
Unfortunately, you can do what you want in a single query. SS doesn't support a function like LIMIT. I'm Actually trying to resolve the problem using the best way.

- ROWCOUNT option of SET statement.
  Causes SS to stop processing the query after the specified number of rows are returned.

set rowcount 3
select * from alpha

/* Result
letter
------
A
B
C
(3 row(s) affected)
*/



- TOP argument of SELECT statement.
  Specifies that only the first n rows are to be output from the query result set.

select top 5 * from alpha

/* Result
letter
------
A
B
C
D
E
(5 row(s) affected)
*/



- IDENTITY function.
  Is used only in a SELECT statement with an INTO table clause to insert an identity column into a new table.
  (This is the best solution I see. However you must create a temporary table to use it)

select identity (int,1,1) as i
      ,letter
  into #alpha
 from  alpha

select letter
  from #alpha
 where i<=5 and i>0

/* Result
letter
------
A
B
C
D
E
(5 row(s) affected)
*/
0
FlubbadubCommented:
Witty,

If you wanted to do Recordset Paging, why didn't you sy so?!! *smiles*

You do know that ADO supports recordset paging... You can tell it how many records are on a recordset "page" and then ask for "page x" of the recordset.  All of this can be done on your asp.. we use this method to page the return of an Item list in a shopping cart  each page has next and back butons... sounds like this would be a more eloquent solution and also has less overhead (you only ever get the number of records you need, ADO deals with the paging!)

Can post code portions (at 50 points) or a full example(Smiling....*)
0
mwetzerAuthor Commented:
Flubbadub,

SOO SORRY - I didn't know, that ASP does support the thing I want :-o (but that is great!)
Tell me, what I have to do, to get the FULL exmample :-)

thanks also to gpbuenrostro!!!!

Michael
0
FlubbadubCommented:
Mwetzer,

I didn't mean to sound offensive - justa little sarcarstic i too took the path you were enquiring about and then found this method.

Here Is the FULL set of functions for dealing with moving recordsets:
<%
'---Initialize vars
Const intPageSize = 20
intCurrPage = Request.QueryString("Page")
If intCurrPage = "" Then intCurrPage = 1


'---Create con & rec, open con
Set con = Server.CreateObject("ADODB.Connection")
Set rec = Server.CreateObject("ADODB.Recordset")

con.Open "FoodMart"


'---Open rec
sq = "SELECT account_num FROM Customer"
rec.CursorLocation = 3 ' = adUseClient
rec.Open sq, con, 3, 1 ' = adOpenStatic, adLockReadOnly
rec.PageSize = intPageSize
intPageCount = rec.PageCount
rec.AbsolutePage = intCurrPage


'---Routines for Moving
If CInt(intCurrPage) <= CInt(intPageCount) _
And CInt(intCurrPage) <> 1 Then
blnMoveFirst = True
Else: blnMoveFirst = False
End If
If CInt(intCurrPage) <= CInt(intPageCount) _
And CInt(intCurrPage) <> 1 Then
blnMovePrev = True
Else: blnMovePrev = False
End If
If CInt(intPageCount) > CInt(intCurrPage) Then
blnMoveNext = True
Else: blnMoveNext = False
End If
If CInt(intPageCount) > CInt(intCurrPage) Then
blnMoveLast = True
Else: blnMoveLast = False
End If

%>
<html><body>
<%


'---Output
For i = 1 To intPageSize
If rec.EOF Then Exit For
Response.Write rec("account_num") & "<br>"
rec.MoveNext
Next


'---Moving links
If blnMoveFirst Then
%>
[<a href="FixedTextSize.asp?Page=1">&lt;|</a>]
<%
End If
If blnMovePrev Then
%>
[<a href="FixedTextSize.asp?Page=<%=intCurrPage - 1%>">&lt;&lt;</a>]
<%
End If
If blnMoveNext Then
%>
[<a href="FixedTextSize.asp?Page=<%=intCurrPage + 1%>">&gt;&gt;</a>]
<%
End If
If blnMoveLast Then
%>
[<a href="FixedTextSize.asp?Page=<%=intPageCount%>">&gt;|</a>]
<%
End If


'---Close objects
rec.Close
con.Close
Set rec = Nothing
Set con = Nothing
%>


</body></html>
Award points as you see fit ;-) it cost me 150 points to get it....
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mwetzerAuthor Commented:
Adjusted points to 150
0
mwetzerAuthor Commented:
Flubbadub,

I hope this will work ;-)

So as you see, I think you should get 150 pts too!!!

Michael
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.