finding all entries with value near intervals of 1000

[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.

[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.

[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;
}

[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.