How to Select Rows in MySQL Starting at a Given Row Number

How can I select rows in MySQL starting at a given row number?

I recommend working by obtaining the first page using:

LIMIT 0, 10

then for the second page

LIMIT 10, 10

then

LIMIT 20, 10

for the third page, and so on.

Select specific row from mysql table

You can use LIMIT 2,1 instead of WHERE row_number() = 3.

As the documentation explains, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return.

Keep in mind that it's an 0-based index. So, if you want the line number n, the first argument should be n-1. The second argument will always be 1, because you just want one row. For example, if you want the line number 56 of a table customer:

SELECT * FROM customer LIMIT 55,1

select rows with particular row-number values

I agree with the other answers, having is not the way, but if you need to use it then:

select id, @r:=@r+1 r
  from test a
  join (select @r:=0) b
having @r+1 <= 10;

Here the demo in SQLFiddle.

There reason why you get the wrong results is because MySql evaluates twice the alias r (in the select and in the having), so @r:=@r+1 is executed twice.

How to select a row based on its row number?

A couple of the other answers touched on the problem, but this might explain. There really isn't an order implied in SQL (set theory). So to refer to the "fifth row" requires you to introduce the concept

Select *
From 
(
    Select 
      Row_Number() Over (Order By SomeField) As RowNum
    , *
    From TheTable
) t2
Where RowNum = 5

In the subquery, a row number is "created" by defining the order you expect. Now the outer query is able to pull the fifth entry out of that ordered set.

Mysql Select X rows after specific match

This is a demo to get expected results:

Select numbers:

SELECT id, number
FROM (
 SELECT b.id, b.number, ROW_NUMBER() OVER(PARTITION BY a.id ORDER BY b.id ASC) r
 FROM numbers a
 JOIN numbers b ON a.id < b.id
 WHERE a.number = 0
) t WHERE t.r <= 5
ORDER BY id

Count numbers:

WITH n AS (
 SELECT id, number
 FROM (
  SELECT b.id, b.number, ROW_NUMBER() OVER(PARTITION BY a.id ORDER BY b.id ASC) r
  FROM numbers a
  JOIN numbers b ON a.id < b.id
  WHERE a.number = 0
 ) t WHERE t.r <= 5
)
SELECT number, COUNT(*) counts
FROM n
GROUP BY number

Sample data:

CREATE TABLE numbers (
id INT PRIMARY KEY auto_increment,
number INT NOT NULL
);

INSERT INTO numbers ( number ) VALUES (1),(0),(9),(14),(11),(0),(3),(4),(10),(9),(0),(5),(3),(11),(0);

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=1fe71080cfb27680eb2a37b721e5de2d

Update for MySQL v5.7

SELECT n.*
FROM numbers n
JOIN (
 SELECT a.id, SUBSTRING_INDEX(GROUP_CONCAT(b.id ORDER BY b.id SEPARATOR ','), ',', 5) selections
 FROM numbers a
 JOIN numbers b ON a.id < b.id
 WHERE a.number = 0
 GROUP BY a.id
) t ON FIND_IN_SET(n.id, t.selections)
ORDER BY n.id

---

SELECT n.number, COUNT(*) counts
FROM numbers n
JOIN (
 SELECT a.id, SUBSTRING_INDEX(GROUP_CONCAT(b.id ORDER BY b.id SEPARATOR ','), ',', 5) selections
 FROM numbers a
 JOIN numbers b ON a.id < b.id
 WHERE a.number = 0
 GROUP BY a.id
) t ON FIND_IN_SET(n.id, t.selections)
GROUP BY n.number
ORDER BY n.number

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=3be09acab5cd696ec4b01585eb5c32ed

MySQL - Get row number on select

Take a look at this.

Change your query to:

SET @rank=0;
SELECT @rank:=@rank+1 AS rank, itemID, COUNT(*) as ordercount
  FROM orders
  GROUP BY itemID
  ORDER BY ordercount DESC;
SELECT @rank;

The last select is your count.

MySQL - Select row number of a record

Try this perhaps:

SET @rownum = 0;
SELECT id 
FROM (SELECT *, @rownum:=@rownum + 1 AS id FROM dict ORDER BY words) d
WHERE d.words LIKE CONCAT('c','%')

As single query, try this:

SELECT id 
FROM (SELECT *, @rownum:=@rownum + 1 AS id FROM dict, (SELECT @rownum:=0) r ORDER BY words) d
WHERE d.words LIKE CONCAT('c','%')

How to get ALL rows starting from row x in MySQL

According to the documentation:

To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. This statement retrieves all rows from the 96th row to the last:

SELECT * FROM tbl LIMIT 95, 18446744073709551615;

This is the maximum rows a MyISAM table can hold, 2^64-1.

There is a limit of 2^32 (~4.295E+09) rows in a MyISAM table. If you build MySQL with the --with-big-tables option, the row limitation is increased to (2^32)^2 (1.844E+19) rows. See Section 2.16.2, “Typical configure Options”. Binary distributions for Unix and Linux are built with this option.

---

Related Topics