Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2010
    Posts
    18

    Question Unanswered: N records above/below the selected record.

    Hii,

    Is there a way to fetch 'n' number of records above or below the selected record.

    example:
    SELECT NAME FROM MyTable
    WHERE NAME LIKE 'Andy'


    Here the query output will be 'Andy'
    So I require 'n' number of records to be displayed above or below 'Andy'

    Kindly Advice
    Thanks
    Last edited by andy982183; 04-02-10 at 09:23.

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by andy982183
    Is there a way to fetch 'n' number of records above or below the selected record.
    You want to look at adding an order by clause and a limit clause to your select statement.

  3. #3
    Join Date
    Mar 2006
    Posts
    56
    Hi,
    Code:
    SELECT M1.name, COUNT(*)
      FROM MyTable M1 INNER JOIN MyTable M2
        ON M1.name >= M2.name
     GROUP BY M1.name
    HAVING COUNT(*) <= (SELECT COUNT(*)
                          FROM MyTable M3
                         WHERE M3.name <= 'Andy');
    This retrieves the results you want, although I am not sure how costly it gets as your table grow (in other words, as you collect more data in there).

    Edit:
    Changing the predicate in the HAVING clause to >=, you will get records of Andy and below.
    If there are records where the name column is NULL, they are not counted in the ordering.
    Last edited by Ikviens; 04-02-10 at 12:46. Reason: edit

Posting Permissions

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