Re: Calculating the range between two numbers

Slightly OT: in MySQL (and most other Relational DBMSes) you can use the IN condition in your select, e.g

SELECT type FROM dog WHERE number IN (2, 3, 4, 5, 6, 7, 8)
[download]

Paul

Comment on Re: Calculating the range between two numbers Download Code

Replies are listed 'Best First'.
Re^2: Calculating the range between two numbers by monarch (Priest) on Jun 24, 2005 at 07:50 UTC
And putting it all together we have: `my @range = ( $c .. $d ); my $rangesql = join( ', ', @range ); my $sql = "SELECT type FROM dog WHERE number IN ( $rangesql )";` [download] Or if you're paranoid: `my @range = ( $c .. $d ); my $rangesql = join( ', ', map { $dbh->quote($_) } @range ); my $sql = "SELECT type FROM dog WHERE number IN ( $rangesql )";` [download]	[reply] [d/l] [select]
Re^3: Calculating the range between two numbers by jhourcle (Prior) on Jun 24, 2005 at 13:45 UTC
If you're going to be using 'IN' a lot, and the set changes, but have a high likelyhood of having the same number of values, you may get better performance by using bind variables. (although, in the cases of numeric ranges, use 'BETWEEN' or <= and >=) `my @set = ($c .. $d); my $sql = 'SELECT type FROM dog WHERE number IN ( ?'.(',?' x $#set).') '; my $sth = $db->prepare($sql) $sth->execute(@set);` [download]	[reply] [d/l]
Re^2: Calculating the range between two numbers by Anonymous Monk on Jun 24, 2005 at 09:31 UTC
Yeah, but that leads to huge query strings if the numbers differ a lot. I'd write it as: `SELECT type FROM dog WHERE number >= 2 AND number <= 8` [download] using placeholders for the two values.	[reply] [d/l]