[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

ms-sql select subset of select

Posted on 2000-01-15
12
Medium Priority
?
359 Views
Last Modified: 2008-01-16
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
0
Comment
Question by:mwetzer
  • 4
  • 3
  • 3
  • +2
12 Comments
 
LVL 4

Expert Comment

by:Gustavo Perez Buenrostro
ID: 2355924
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
 
LVL 2

Expert Comment

by:highmarks
ID: 2356816
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
 

Author Comment

by:mwetzer
ID: 2357226
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 4

Expert Comment

by:Gustavo Perez Buenrostro
ID: 2359690
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
 
LVL 3

Expert Comment

by:Flubbadub
ID: 2378593
You could do your select into a temporary table with a primary key Identity 1,1 and then use the >= 100 <= Method outlined???
0
 
LVL 1

Expert Comment

by:witty
ID: 2378946
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
 
LVL 4

Expert Comment

by:Gustavo Perez Buenrostro
ID: 2379583
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
 
LVL 3

Expert Comment

by:Flubbadub
ID: 2379995
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
 

Author Comment

by:mwetzer
ID: 2381752
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
 
LVL 3

Accepted Solution

by:
Flubbadub earned 600 total points
ID: 2383711
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
 

Author Comment

by:mwetzer
ID: 2384632
Adjusted points to 150
0
 

Author Comment

by:mwetzer
ID: 2384636
Flubbadub,

I hope this will work ;-)

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

Michael
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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.
Suggested Courses

591 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