Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Jul 2006
    Posts
    8

    Unanswered: retriving previous 5 rows and next 5 rows and the searched record

    Dear All

    I have a table with the following structure in sql server 2005

    create table app(
    sno int,
    name varchar(50),
    add varchar(50),
    city varchar(50),
    state varchar(50)
    )

    it contains the follwing data
    ------------------------------------------
    sno name add city state
    ------------------------------------------
    1 mark street no1 newcity newstate
    2 mark street no1 newcity newstate
    3 mark street no1 newcity newstate
    4 mark street no1 newcity newstate
    5 mark street no1 newcity newstate
    6 mark street no1 newcity newstate
    7 mark street no1 newcity newstate
    8 mark street no1 newcity newstate
    9 mark street no1 newcity newstate
    10 mark street no1 newcity newstate
    11 mark street no1 newcity newstate
    12 mark street no1 newcity newstate
    13 mark street no1 newcity newstate
    14 mark street no1 newcity newstate
    15 mark street no1 newcity newstate
    16 mark street no1 newcity newstate
    17 mark street no1 newcity newstate
    18 mark street no1 newcity newstate
    19 mark street no1 newcity newstate
    20 mark street no1 newcity newstate

    ----------------------------------------

    I want to retrive previous 5 records, next 5 records and the record that meet the where condition of a select query.


    When I run

    select sno,add,name,city,state from app where sno=7

    I want the following result

    ------------------------------------------
    sno name add city state
    ------------------------------------------
    2 mark street no1 newcity newstate |
    3 mark street no1 newcity newstate |
    4 mark street no1 newcity newstate | -- previous 5 records
    5 mark street no1 newcity newstate |
    6 mark street no1 newcity newstate |
    7 mark street no1 newcity newstate --- searched record
    8 mark street no1 newcity newstate |
    9 mark street no1 newcity newstate |
    10 mark street no1 newcity newstate |--- next 5 records
    11 mark street no1 newcity newstate |
    12 mark street no1 newcity newstate |
    ----------------------------------------

    if there is a method to get the above result set, kindly post the query.
    Last edited by markandeyulup; 03-31-08 at 02:50.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I want five million dollars so I can live off of the interest and never work again.

    read the directions under the post "How to ask a question to get quick and correct answers" and try again.

    http://www.dbforums.com/showthread.php?t=1212452
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Define what you mean by "next record" and "previous record".
    How many rows does your where condition return?

    Post a complete question please.
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    To solve your problem as posted you could use:
    Code:
    SELECT *
       FROM app
       WHERE sno BETWEEN 2 AND 12
    -PatP

  5. #5
    Join Date
    Jul 2006
    Posts
    8
    Dear Mr PatP

    thanks for the reply. The sno column may not be continuous and may not be numeric and duplicates may exists.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by markandeyulup
    The sno column may not be continuous and may not be numeric and duplicates may exists.
    This table has a PK correct? You will need some unique combination of columns otherwise you will get ties which may span the top\ bottom end of your bands.

    And you lot are big meanies too
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jun 2007
    Location
    Ohio, USA
    Posts
    142
    If [sno] is an int, then I'm pretty sure it will always be numeric...
    David Maxwell
    Data Integrity? Yeah, I've heard of that...

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select sno,add,name,city,state 
      from app as t
     where /* this */ 
           sno = 7
        or /* prev 5 */
           ( select count(*)
               from app
              where sno > t.sno 
                and sno < 7 ) < 5
        or /* next 5 */
           ( select count(*)
               from app
              where sno < t.sno 
                and sno > 7 ) < 5
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Rudy dude:
    Doesn't work for me:
    Code:
    create table app(
    sno int,
    name varchar(50),
    [add] varchar(50),
    city varchar(50),
    state varchar(50)
    )
    
    INSERT INTO app
    SELECT    1, 'mark', 'street no1', 'newcity', 'newstate' UNION ALL
    SELECT    2, 'mark', 'street no1', 'newcity', 'newstate' UNION ALL
    SELECT    3, 'mark', 'street no1', 'newcity', 'newstate' UNION ALL
    SELECT    4, 'mark', 'street no1', 'newcity', 'newstate' UNION ALL
    SELECT    5, 'mark', 'street no1', 'newcity', 'newstate' UNION ALL
    SELECT    6, 'mark', 'street no1', 'newcity', 'newstate' UNION ALL
    SELECT    7, 'mark', 'street no1', 'newcity', 'newstate' UNION ALL
    SELECT    8, 'mark', 'street no1', 'newcity', 'newstate' UNION ALL
    SELECT    9, 'mark', 'street no1', 'newcity', 'newstate' UNION ALL
    SELECT    10, 'mark', 'street no1', 'newcity', 'newstate' UNION ALL
    SELECT    11, 'mark', 'street no1', 'newcity', 'newstate' UNION ALL
    SELECT    12, 'mark', 'street no1', 'newcity', 'newstate' UNION ALL
    SELECT    13, 'mark', 'street no1', 'newcity', 'newstate' UNION ALL
    SELECT    14, 'mark', 'street no1', 'newcity', 'newstate' UNION ALL
    SELECT    15, 'mark', 'street no1', 'newcity', 'newstate' UNION ALL
    SELECT    16, 'mark', 'street no1', 'newcity', 'newstate' UNION ALL
    SELECT    17, 'mark', 'street no1', 'newcity', 'newstate' UNION ALL
    SELECT    18, 'mark', 'street no1', 'newcity', 'newstate' UNION ALL
    SELECT    19, 'mark', 'street no1', 'newcity', 'newstate' UNION ALL
    SELECT    20, 'mark', 'street no1', 'newcity', 'newstate'
    
    DECLARE    @sno AS INT
    SELECT    @sno = 7;
    
    WITH numberised--numberised set
    AS
        (
            SELECT    app.sno
                    , app.[add]
                    , app.name
                    , app.city
                    , app.state 
                    , nzdf_lite        = ROW_NUMBER()    OVER    (ORDER BY    app.sno)
            FROM    app
        )
    SELECT    numberised.sno
            , numberised.[add]
            , numberised.name
            , numberised.city
            , numberised.state
    FROM    numberised
    CROSS JOIN 
            (SELECT    nummy    = MAX(CASE WHEN sno = @sno THEN nzdf_lite END)
            FROM    numberised) AS numero
    WHERE    numberised.nzdf_lite - numero.nummy BETWEEN -5 AND 5
    
    select sno,[add],name,city,state 
      from app as t
     where /* this */ 
           sno = 7
        or /* prev 5 */
           ( select count(*)
               from app
              where sno > t.sno 
                and sno < 7 ) < 5
        or /* next 5 */
           ( select count(*)
               from app
              where sno < t.sno 
                and sno > 7 ) < 5
    
    DROP TABLE app
    Mine does
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump
    Mine does
    show yours, then
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It's in - from "WITH numerised" downwards. Parameterised too
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    but you prefaced that code block (which i did not read, although i recognized the last part) with Doesn't work for me:

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    but you prefaced that code block (which i did not read, although i recognized the last part) with Doesn't work for me:

    But.... it.... er.... - (insert your own onomatopoeic for raspberry)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You not fixing it then Rude boy? On a heap yours is more efficient than mine but a clustered unique index on sno brings me up hot on your SQL heels.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, no time

    may look at it tomorrow, if the original poster reports that mine doesn't actually work (i did not test it)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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