phpmyadmin Question posted in
The official documentation can be found here.

Using Limit in SQL according to row_count – Phpmyadmin

I want to fetch number of rows with highest number of multiple of 20, Like if my table have 148 rows then limit should be 140 leaving the latest 8 entry behind, or if my table have 170 rows then limit will be 160. What will be the query in this case.

$conn = mysqli_connect($server_name, $mysql_username, $mysql_password, 
$db_name);
if($conn === false){
die("ERROR: Could not connect. " . mysqli_connect_error());
}

 $number= $_POST['number'];


 $sql1 = "SELECT * FROM abc_table LIMIT WHAT TO ENTER HERE  ";
Tags:

2

Answers


  1. 0

    As far as I know, what follows LIMIT has to be an integer literal. LIMIT won’t even take something like 6/2, which would evaluate to an integer literal. I recommend just reading in the entire table, and then only processing how many rows you need in PHP.

    $row_cnt = $result->num_rows;
$rs_size = $row_cnt - ($row_cnt % 20);

while ($rs_size > 0 && $row = mysqli_fetch_assoc($result)) {
    // process a row
    --$rs_size;
}

    The above while loop should exit after reading the greatest number of multiples of 20 available. This approach is not too wasteful, since at most you would be reading in 19 extra rows from MySQL which you would end up not using.

    Login or Signup to reply.
  2. 0

    You can use variables for this:

    select t.*
from (select t.*, (@rn := @rn + 1) as rn
      from t cross join
           (select @rn := 0) params
      order by ?
     ) t
where rn <= floor(rn / 20) * 20;

    The ? is for the column used to specify the ordering, presumably something like id asc.

    In MySQL 8+, you would use window functions:

    select t.*
from (select t.*,
             row_number() over (order by ?) as seqnum,
             count(*) over () as cnt
      from t
     ) t
where seqnum <= floor(cnt / 20) * 20;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search