Results 1 to 3 of 3
  1. #1
    Join Date
    May 2012
    Posts
    1

    Unanswered: Fetching Next previous and current rows

    I am trying to create a stored procedure where i would like to fetch the next previous and current rows from a particularl pagename which is stored in a table with sorting of dateadded

    like my products table structure

    id
    iQty
    strProductTitle
    strPageName
    DtAdded
    DtApproved


    my dummy records are like

    1 13 'PCHDD' 'PCHDD' '2009-12-03 04:32:30.363' '2009-12-04 04:32:30.363'
    2 30 'SDRAM' 'SD_Ram' '2009-12-03 04:32:30.363' '2009-12-06 04:32:30.363'
    3 12 'Pen Drive' 'Pendrive' '2009-12-03 04:32:30.363' '2009-12-05 04:32:30.363'
    4 3 'Note Book' 'NoteBook' '2009-12-03 04:32:30.363' '2009-12-08 04:32:30.363'
    5 15 'VIO' 'VIO' '2009-12-03 04:32:30.363' '2009-12-06 04:32:30.363'
    6 19 'PS2' 'PS_2' '2009-12-03 04:32:30.363' '2009-12-09 04:32:30.363'
    7 31 'PS3' 'PS_3' '2009-12-03 04:32:30.363' '2009-12-02 04:32:30.363'
    8 23 'WII' 'WII' '2009-12-03 04:32:30.363' '2009-12-10 04:32:30.363'
    9 22 'Speakers' 'Speakers' '2009-12-03 04:32:30.363' '2009-12-16 04:32:30.363'
    10 12 'Iphone' 'I_phone' '2009-12-03 04:32:30.363' '2009-12-15 04:32:30.363'


    now if i query for a record like
    I_phone
    i should get

    Prv Detail I_pad row
    11 23 'Ipad' 'I_pad' '2009-12-03 04:32:30.363' '2009-12-11 04:32:30.363'
    Current Detail I_phone
    10 12 'Iphone' 'I_phone' '2009-12-03 04:32:30.363' '2009-12-15 04:32:30.363'
    Next Record Speakers
    9 22 'Speakers' 'Speakers' '2009-12-03 04:32:30.363' '2009-12-16 04:32:30.363'


    please help in creating a stored procedure

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Maybe something like that:

    Code:
    with 
        CTE_RowSel as
        (
            select * from products where stPageName = 'I_phone'
        ),
    
        CTE_Rows as
        (
            select * from CTE_RowSel
    
            union all
    
            select top 1 * from products 
            where id < (select id from CTE_RowSel)
            order by id desc
    
            union all
    
            select top 1 * from products 
            where id > (select id from CTE_RowSel)
            order by id
        )
    
    select * from CTE_Rows
    Hope it's useful.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this?

    Code:
    WITH
     numbered_products AS (
    SELECT p.*
         , ROW_NUMBER()
              OVER( ORDER BY id ) AS row_num
     FROM  products p
    )
    SELECT id
         , iQty
         , strProductTitle
         , strPageName
         , DtAdded
         , DtApproved 
     FROM (SELECT row_num
            FROM  numbered_products
            WHERE strPageName = 'I_phone'
          ) c
     INNER JOIN
           numbered_products p
      ON   ABS(p.row_num - c.row_num) <= 1
    /* or
      ON   p.row_num BETWEEN c.row_num - 1
                         AND c.row_num + 1
    */
    ;

Tags for this Thread

Posting Permissions

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