Member Avatar for tustind

I have a table in which each entry has a view counter. I would like to create a list that displays only entries that are within 100 views of a 1,000 view threshold. But, I want to also show each entry at any interval of 1,000 views. I.E., I would want to show all the following entries:

View Count:
975
1025
1980
2020
4960
5045
9990
10030
45980
46020
etc...

I am not quite sure the best way to do this, or if it is possible, in a MYSQL query.

I was thinking something along the lines of dividing each view count by 1000, and if the result is between x.95 - x.05 showing the row.

Any help on how to implement this, or a better solution, would be appreciated.

  • 2 Contributors
  • 3 Replies
  • 120 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by edwinhermann

Dani AI

Generated

A simple, robust way to detect "within 100 of any 1,000" is to test the remainder when dividing by 1000. This avoids floating rounding quirks and automatically covers every 1,000 interval. 's ROUND-based idea is valid; correctly pointed out the low-count edge that you should explicitly exclude (or ensure the computed interval is > 0).

SELECT *
FROM poll_questions
WHERE (views % 1000 <= 100 OR views % 1000 >= 900) AND views > 100
ORDER BY views DESC;

For larger tables, avoid scanning the whole table every time. MySQL 5.7+ supports stored generated columns you can index; store the remainder (or the thousand-interval number) and filter on that indexed column for much faster queries. If you cannot use generated columns, maintain the value with a trigger or in your application code.

ALTER TABLE poll_questions
  ADD COLUMN rem INT GENERATED ALWAYS AS (views % 1000) STORED,
  ADD INDEX idx_rem (rem);

To show rows closest to the thousand first, order by the distance to the nearest thousand (use LEAST(rem, 1000-rem) once you have rem). For small datasets the inline modulo query is simplest; for production-scale tables add a computed/indexed column or precompute ranges in application logic to keep queries fast.

Recommended Answers

All 3 Replies

Member Avatar for edwinhermann

I have a table in which each entry has a view counter. I would like to create a list that displays only entries that are within 100 views of a 1,000 view threshold. But, I want to also show each entry at any interval of 1,000 views. I.E., I would want to show all the following entries:

View Count:
975
1025
1980
2020
4960
5045
9990
10030
45980
46020
etc...

I am not quite sure the best way to do this, or if it is possible, in a MYSQL query.

I was thinking something along the lines of dividing each view count by 1000, and if the result is between x.95 - x.05 showing the row.

Any help on how to implement this, or a better solution, would be appreciated.

This should do it:

SELECT *,ROUND(views/1000,0) intervals FROM poll_questions WHERE views >= (ROUND(views/1000,0) * 1000 - 100) and views <= (ROUND(views/1000,0) * 1000 + 100) ORDER BY intervals ASC, views ASC

Here I've assumed "views" is the name of the column holding the views.

I have tested this code and it works.

Member Avatar for tustind

This should do it:

SELECT *,ROUND(views/1000,0) intervals FROM poll_questions WHERE views >= (ROUND(views/1000,0) * 1000 - 100) and views <= (ROUND(views/1000,0) * 1000 + 100) ORDER BY intervals ASC, views ASC

Here I've assumed "views" is the name of the column holding the views.

I have tested this code and it works.

Thanks, I tested this and it works. The only bug is that it will also return any row with views <= 100, but this is fixed simply by modifying the code to exclude views <= 100.

I ended up using:

SELECT * FROM table WHERE views >= (ROUND(views/1000,0) * 1000 - 100) AND views <= (ROUND(views/1000,0) * 1000 + 100) AND views > 100 ORDER BY views DESC

I also wrote a PHP function to accomplish a similar goal -- determine if a number is within a given range of a given threshold:

function near_threshold($num,$threshold,$range){
  $range = $range/$threshold;
  $a = $num/$threshold;
  $b = round($a, 0);
  $c = $a-$b;
  $d = abs($c);
  
  if($d < $range && $b != 0){
	  return TRUE;
  }
  else
  	return FALSE;
}
Member Avatar for edwinhermann

Yeah, true I did notice it returned the 0-rated ones.

I ended up using:

SELECT * FROM table WHERE views >= (ROUND(views/1000,0) * 1000 - 100) AND views <= (ROUND(views/1000,0) * 1000 + 100) AND views > 100 ORDER BY views DESC

That would work, though I would have chosen AND (ROUND(views/1000,0)) > 0 as my condition.

Either or - it's just a matter of preference I guess.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.