Post reply

How to get the last record from cursor?.

  • 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

  • August 4, 2004 at 3:43 am

    #517576

    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

    #517779

    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

    #517783

    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

    #517808

    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

  • August 5, 2004 at 7:59 am

    #517834

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

  • August 5, 2004 at 8:12 am

    #517845

    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

    #517886

    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:

    1. Query your statement into Variables
    2. 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 @userid int

    --Set @userid = 315

    Select Top 1 @Role = Role, @SchoolID = SchoolID From dbo.Users Where UserID = @userid 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 7 (of 7 total)

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