CODEWITHSUNDEEP
mysql
Sam San
Sam San

Reputation: 6903

mysql select while skipping some number of rows

Is there a mysql syntax that can hop some rows?

For example

id          value
1             a 
2             b
3             c
4             d
5             e
6             f
7             g
8             h
9             i

SELECT * FROM table HOP BY 2

so the result will be

id          value
3             c
6             f
9             i

or

id          value
1             a 
4             d
7             g

Take note: We don't know the actual ID of a row so we can't use a WHERE clause like this

WHERE ID is a multiple of 3 or etc.

Upvotes: 0

Views: 69

Answers (3)

danielkza
danielkza

Reputation: 2871

SET @row_idx := 0;
SELECT *, (@row_idx := @row_idx + 1) AS idx
FROM table
HAVING idx % 3 = 0;

You probably want to include an ORDER BY clause so the row index can actually be meaningful though. You can't rely on the result being ordered by id without specifying it.

Upvotes: 0

James G.
James G.

Reputation: 2904

I didn't realize you could do math in sql queries. Learned something new. Cool. Here's code that would select 1, 4, and 7.

$stmt = mysqli_prepare($connection, "SELECT * FROM users WHERE (ID+2)%3 = 0 AND ID>1");
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
while($row = mysqli_fetch_assoc($result)){
    echo $row['username'];
}

I don't see why it wouldn't work if id has gaps, as the man with ?mandarin? symbols for a name said.

Modulus(%), if you don't know, gives the number of decimals given by a division problem. So 3/3=1, with no decimals, so 3%3=0, whereas 4/3=1.333333..., so 4/3 equals infinity(not really in programming, but close enough).

Upvotes: 1

Arun Killu
Arun Killu

Reputation: 14233

SELECT * FROM hoptable WHERE ID%3 =0 AND ID>1

Upvotes: 1

Related Questions