Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2009
    Posts
    4

    Unanswered: Change this code to select the 2nd row instead of the 1st please

    SQL 2000


    Code:
    ISNULL((SELECT top 1 i.field
    	from table ta
    	inner join blabla on blabla = blabla and blabla = blabla
    	inner join blabla on blabla = blabla
    	inner join blabla on blabla = blabla
    	inner join blabla on blabla = blabla
    	where blabla = blabla and ISNULL(blabla,0) = 0
    	and ISNULL(blabla,0) = 1 and ISNULL(bla.Date, getDate()) <  getDate()+ 1 and  ISNULL(bla.Date2, getDate()) <  getDate()+ 1),'') [field],
    Thanks

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Pseudo code:
    Code:
    SELECT TOP 1
           *
    FROM   (
            SELECT TOP 2
                   *
            FROM   table
            ORDER
                BY field ASC
           ) As a
    ORDER
        BY field DESC
    Microsoft SQL Server 2000?
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2009
    Posts
    4
    Correct, MS SQL 2000, I attempted that method but kept getting errors, something in my syntax must be wrong, where would all of my inner joins and where's go ?

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Care to share the error messages?

    Change your top 1 to a top 2, then wrap that entire statement in the top one with an opposite order.

    Moved to Microsoft SQL Server topic
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2009
    Posts
    4
    Quote Originally Posted by gvee
    Care to share the error messages?

    Change your top 1 to a top 2, then wrap that entire statement in the top one with an opposite order.

    Moved to Microsoft SQL Server topic
    Sorry, I'm new at this so I'm not sure what you mean by opposite order, I attempted this--
    Code:
    SELECT TOP 1
           *
    FROM   (
    ISNULL((SELECT top 2 i.field
    	from table ta
    	inner join blabla on blabla = blabla and blabla = blabla
    	inner join blabla on blabla = blabla
    	inner join blabla on blabla = blabla
    	inner join blabla on blabla = blabla
    	where blabla = blabla and ISNULL(blabla,0) = 0
    	and ISNULL(blabla,0) = 1 and ISNULL(bla.Date, getDate()) <  getDate()+ 1 and  ISNULL(bla.Date2, getDate()) <  getDate()+ 1),'') [field],
    ) As a
    ORDER
        BY field DESC

    but got errors,

    Msg 156, Level 15, State 1, Line 24
    Incorrect syntax near the keyword 'SELECT'.
    Msg 170, Level 15, State 1, Line 27
    Line 27: Incorrect syntax near '('.
    Msg 170, Level 15, State 1, Line 34
    Line 34: Incorrect syntax near ','.
    Msg 170, Level 15, State 1, Line 51
    Line 51: Incorrect syntax near ','.
    Msg 170, Level 15, State 1, Line 60
    Line 60: Incorrect syntax near ','.
    Msg 170, Level 15, State 1, Line 64
    Line 64: Incorrect syntax near ','.
    Msg 170, Level 15, State 1, Line 69
    Line 69: Incorrect syntax near ','.
    Msg 170, Level 15, State 1, Line 73
    Line 73: Incorrect syntax near ','.
    Msg 170, Level 15, State 1, Line 77
    Line 77: Incorrect syntax near ','.
    Msg 170, Level 15, State 1, Line 82
    Line 82: Incorrect syntax near ','.
    Msg 170, Level 15, State 1, Line 99
    Line 99: Incorrect syntax near 'loc2'.
    Msg 170, Level 15, State 1, Line 110
    Line 110: Incorrect syntax near ')'.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You have a few syntax errors in your very first query as it is.
    I've re-written it as best as possible
    Code:
    SELECT TOP 1
           field
    FROM   (
            SELECT TOP 2
                   i.field As field
            FROM   table_a As a
             INNER
              JOIN table_b As b
                ON a.blah = b.blah
             INNER
              JOIN table_c As c
                ON a.blah = c.blah
            WHERE  a.blabla = 'blabla'
            AND    Coalesce(a.blabla, 0) = 0
            ORDER
                BY i.field ASC
           ) As a_subquery
    ORDER
        BY field DESC
    George
    Home | Blog

Posting Permissions

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