N records above/below the selected record.

andy982183 · #1 (**permalink**) 04-02-10, 08:16

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

mike_bike_kite · #2 (**permalink**) 04-02-10, 08:35

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.

Ikviens · #3 (**permalink**) 04-02-10, 11:02

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech