Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2002
    Location
    Houston, Texas
    Posts
    85

    Unanswered: How to get the second row of a recordset?

    Here's my SQL Statement (I'm using MS SQL 2000):

    SELECT TOP 2 MenuComments, MenuDate, MenuID, MenuIsActive, MenuName
    FROM Menu
    ORDER BY MenuDate DESC

    This orders the data correctly, but the problem is, I need ONLY the SECOND row, not the top row. Also, because I am sorting for menus entered into the system, I cannot use a variable based on real dates (in other words, I can't use the server clock to help filter the results).

    Any and all help would be GREATLY appreciated -- I've been banging my head against this one all day!

    Mike

  2. #2
    Join Date
    Aug 2006
    Posts
    7
    You want the last row? Is that correct? Use the MoveLast method of the ADO recordset object. This will take you to the second row using a top 2.

    eg:
    dim Conn = "..... Your connection string ....."

    Set dbAPI = Server.CreateObject("ADODB.Connection")
    set rs1 = Set rs1 = Server.CreateObject("ADODB.Recordset")

    myCmd = "select .........."

    dbAPI.Open Conn

    set rs1 = dbAPI.Execute myCmd

    if rs1.EOF = false then

    rs1.MoveLast

    myvar1 = rs1("myCol1")
    myvar2 = rs1("myCol2")
    ... and so on

    end if

    dbAPI.Close

    Hope this helps.

  3. #3
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506

    SELECT TOP 1 * FROM
    (SELECT TOP 2 * FROM Menu
    ORDER BY MenuDate DESC) Menu
    ORDER BY Menu.MenuDate
    I hope this will solve your problem.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  4. #4
    Join Date
    Nov 2002
    Location
    Houston, Texas
    Posts
    85
    Thank you both VERY MUCH! The move last would work (can't believe I didn't think of it). I decided to use Rudra's subquery because it was faster (in other words, less typing for me).

    Thanks again for solving a problem that was driving me crazy!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •