CODEWITHSUNDEEP
mysqldatabase
Jay Marz
Jay Marz

Reputation: 1912

How count the number of rows starting from a specified row?

I have a query problem about retrieving number of rows that the counting will start from the specified row. These are the row values on my MySQL database.

John Parker
Tony Graham
Perter Smith
Annabelle Sergio
Kris Pata
Neshren Luca
Paul Pon
Zervich Nuckrav
Allan Paulson
Imanu Hashmarida
Varick Dagovich
Senster Burgsman
Lito Umani
Ramsay Nudillo

Now I want to retrieve the first 5 row that will start from Neshren Luca so that the resultset may look like this:

Neshren Luca
Paul Pon
Zervich Nuckrav
Allan Paulson
Imanu Hashmarida

How may I do that?

Note: I will not try to retrieve the row values base on number of row but base from a specific row value.

Upvotes: 1

Views: 115

Answers (3)

John Woo
John Woo

Reputation: 263733

This looks very ugly but have this a try, this uses local variable

SET @selectedName := 'Neshren Luca'; -- set the name here
SET @selectRow :=
          (
            SELECT RankNo
            FROM
                (
                    SELECT @rowNum := @rowNum + 1 AS RankNo,
                           a.Names
                    FROM   tableName a, (SELECT @rowNum:= 0) b
                ) x
            WHERE  Names = @selectedName
          );

SELECT Names
FROM
  (
    SELECT @rowNum1 := @rowNum1 + 1 AS RankNo, a.Names
    FROM   tableName a, (SELECT @rowNum1:= 0) b
  ) x
WHERE  RankNo BETWEEN @selectRow AND @selectRow + 4

Upvotes: 2

user2008074
user2008074

Reputation:

SQL engine may have some 'natural' row order, but it is not guaranteed. You should have primary key, say ID, then ORDER BY ID, find first ID WHERE your string is. And then select first N items WHERE ID >= that id.

Edit: Or, if your database guarantees natural order, find row id/index and LIMIT/OFFSET by this value. SQLite has built-in natural rowid for each table row, for example.

Upvotes: 0

Rajesh Uddem
Rajesh Uddem

Reputation: 11

Looks like the above select statement will work fine for that..other wise you have to do other way but it will bit lengthy..that.. -> You have to get the above records into a cursor -> Looping through the cursor and getting the records..

Upvotes: 0

Related Questions