SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    Michigan
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Selecting above and below

    I have a table full of names.

    I have the id for one of those names and Id like to sort the list by the name and then get 10 results above the name as well as 10 below (assuming there are 10 above and below) the row with the id I have. I'm not sure if this is possible in one query but thats okay.

    As a quick example:

    Code:
    14      Dave
    19 	Eric
    15 	Jack 	 
    10 	James 	 
    8       Mark
    9   	Mike
    16 	Ralph 	 
    18 	Sam 	 
    17 	Simon 	 
    11 	Tom 	 
    12 	Tony 	 
    13 	Troy
    If I had the id 16 (for ralph) and I wanted the 3 above and below then:
    Code:
    10 	James 	 
    8       Mark
    9   	Mike
    and

    Code:
    18 	Sam 	 
    17 	Simon 	 
    11 	Tom
    Ive been trying to figure this out all night but I cannot.

    Thanks for any help.

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    (select id
          , name
       from theTable
      where name > (select name
                      from theTable
                     where id = 16)
     order
         by name
      limit 3)
     union all
    (select id
          , name
       from theTable
      where id = 16)
     union all
    (select id
          , name
       from theTable
      where name < (select name
                      from theTable
                     where id = 16)
     order
         by name desc
      limit 3)
    order
        by name
    you might have to twiddle with the order by's


Bookmarks

Posting Permissions

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