Using Limit in SQL according to row_count

Question

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  ";

How about selecting a select count from the table first, doing a div operation and using the output of that as your limit? — Dijkgraaf
– Dijkgraaf, Commented Dec 25, 2018 at 8:45

Tim Biegeleisen · Accepted Answer · 2018-12-25 08:07:14Z

1

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.

answered Dec 25, 2018 at 8:07

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mayank Bhatnagar Over a year ago

Thanks Tim for your response let me try this and get back to you.

Gordon Linoff · Accepted Answer · 2018-12-25 14:00:35Z

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;

answered Dec 25, 2018 at 14:00

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Using Limit in SQL according to row_count

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related