August 3, 2004 at 11:51 pm
Hi,
I am having one cursor, it returns five records. At that time, I want to get the fifth record and doing some modification. How to get the fifth record alone?.
Some time, the cursor will return four records, at that time, I want to get the fourth record. How to get the last record from cursor?.
Regards
-Gopi
August 4, 2004 at 3:43 am
Hi Gopinath!
Would it be possible for you to use "TOP 1" and "ORDER BY" in your definition for the cursor so it only return one row for you?
robbac
___the truth is out there___
August 5, 2004 at 2:34 am
You can declare a scollable cursor and then fetch last
eg..
declare my_cursor SCROLL CURSOR for select my_attribute from my_table
open my_cursor
fetch last from my_cursor into @my_variable
August 5, 2004 at 3:04 am
Hi!
You can, after open the cursor use FETCH LAST FROM <cursor_name> INTO <variable>
Good luck!
robbac
___the truth is out there___
August 5, 2004 at 6:37 am
August 5, 2004 at 7:59 am
The subject is how to get the last record from a cursor.....
August 5, 2004 at 8:12 am
Indeed it is, as well as suggestions how to solve the problem.
robbac
___the truth is out there___
August 5, 2004 at 11:28 am
Despite the fact that the subject is how to get the last record from a cursor, the question one should ask themself is this: "Is there a more efficient way to get the job done?" I will have to concur with Tal Mcmahon that a cursor is not the best way to get the job done. Here are two alternatives that I believe are better:
Here is an example of creating variables and querying your data into them:
Declare @Role varchar(50)
Declare @SchoolID int
--Declare @user-id int
--Set @user-id = 315
Select Top 1 @Role = Role, @SchoolID = SchoolID From dbo.Users Where UserID = @user-id Order by UserID Desc
I do not know why your query is returning 4 or 5 items, but if you know that you need the last one then you should be able to Order Desc and use the TOP 1, as Robbac so kindly explained.
Or Use a Temp Table...
Here is a sample of a Temp Table:
SELECT * INTO #MyTempTable FROM MyTable
I love temp tables because then if you really had to you could use a WHILE statement to loop through the temp table until you came to the last of your 4 or 5 records and then you would have the last one that you could work with. This type of thing is much more efficient that a cursor...trust me.
Of course this is an alternative path, but by doing this type of thing and moving away from cursors I have literally saved hours in processing time.
Have a great day!
Scott
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy