How to get the last record from cursor?.

Question

How to get the last record from cursor?.

Gopinath-155584

SSC Enthusiast

Points: 178
More actions
August 3, 2004 at 11:51 pm

#163034

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Robbac Ten Centuries Points: 1090 More actions · Answer 1

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___

David Gilkinson Ten Centuries Points: 1334 More actions · Answer 2

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

Robbac Ten Centuries Points: 1090 More actions · Answer 3

Hi!

You can, after open the cursor use FETCH LAST FROM <cursor_name> INTO <variable>

Good luck!

robbac
___the truth is out there___

Tal Mcmahon SSC Eights! Points: 850 More actions · Answer 4

I would like to emphasize that robbac had the correct solution.

Using a cursor is generally bad mojo--especially if you can just query to get a specific record do that.

a cursor is not needed and is very costly in this instance.

t

Kindest Regards,

Tal Mcmahon

David Gilkinson Ten Centuries Points: 1334 More actions · Answer 5

The subject is how to get the last record from a cursor.....

Robbac Ten Centuries Points: 1090 More actions · Answer 6

Indeed it is, as well as suggestions how to solve the problem.

robbac
___the truth is out there___

Scott Shepherd SSC Rookie Points: 29 More actions · Answer 7

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:

Query your statement into Variables
Use a Temp table

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