March 22, 2002 at 9:36 am
Hello everybody!
Let suppose I have 12 records in a table.
Each time when I call the stored procedure I want only 5 records from this table. With the first 5 I use top 5.
How can I select the next 5 records next time when I call the stored procedure. I can pass the paramater each time when I run the stored procedure.(1- first time, 6-second time and so on)
Thank you!!!
Radu
March 22, 2002 at 10:12 am
How about using an Identity column. This will number your records sequentially. Then all you need do is select the records with an identity value between your first and last desired numbers. I gues you would need to store the highest number you selected last time and add 1 to that to give you the starting number for your next select.
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
March 22, 2002 at 11:02 am
As long as you have a value you can use for a > statement that will make each record unique and they can be order that way, then you can do this sort of thing without an id
SELECT TOP 5 T1.uniquevalue FROM tblX T1 WHERE (numbertostartat-1) = (SELECT count(*) FROM tblX T2 WHERE T2.uniquevalue > T1.uniquevalue) ORDER BY T1.uniquevalue
The only problem is on large tables where this can cause some delays in output and creates a good deal of overhead. Otherwise try to create an identity field and do as suggested.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 22, 2002 at 11:34 am
Jon posted a couple articles you might find interesting:
Andy
March 22, 2002 at 11:44 am
Thanks!
But I can't use the ID because the records that I want to extract are already from a select statement from a table.
What if a parse the table each time and I insert the records that I need (between 6 and 10 for example) in a temporary table and then use a select statement for this table and send it to VB. Do you think that it will destroy the perormance of the server? I'm afraid it will.
My real scenario is that I have to be able to navigate through records in a web page but I don't know how many rows will be on each page.
Thanks
March 22, 2002 at 11:53 am
Who is going to see this output and how? If the users thru an application interface you built such as web or VB you can select top records to the highest they need and cursor to say the 6th record in a client side cursor then display only 6-10. Just how will it be presented and to who?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 22, 2002 at 12:03 pm
Put the required data into a temp table and select it from there. here you want to select top 5 records, select them and insert into a temp table. Next time when you want to select the next 5,
select top 5 from main_table where primarykeyfieldvalue not in temp_table
I think this is what has been done in the sp_getorders stored procedure in the above given link.
Paras Shah
Evision Technologies
Mumbai, India
Paras Shah
Evision Technologies
Mumbai, India
March 22, 2002 at 12:23 pm
It's a web application with, lets say it, 12 orderheaders. The requirement is that after the header to display also the details. (something like: header,detail,header,detail,etc.) So I made a join for orderheader and orderdetail.
This 12 orderheaders will be 120 records, but that dosn't mean that each header will have 10 line with orderdetail. Here is my problem. each header can have from 1 record of detail to 250. So when I bring up the page I want to display only the first 5 headers but I don't know how many line this will be.
The solution that I found right now is that when I start, I retrieve all orderheaders (the numbers, nothing else) and store it in a array. The first 5 orderheaders will be displayed with top5 option. If I click next I will parse the array in ASP where I stored the orderheader numbers. And I will send to the stored procedure only the orderheaders 6 to 10th. in the stored procedure I have a select statement where the condition will be "in @Orderheader1,@Orderheader2...,@orderheader5"
But do you think guys. Will this kill my server? I will have like 100 clients and each PO between 3 to 15 orderheaders
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply