CODEWITHSUNDEEP
mysql
Danielo515
Danielo515

Reputation: 7161

Mysql get max value in several ranges

This question is regarding mysql. I want to get the maximum value in several groups of min and max values. There is no group column defined, the "groups" are based on ranges that I define. I think this is explained better with an example. What I want is the following set of queries into a single one:

select max(value) from tablename where value between 5000 and 5499;
select max(value) from tablename where value between 7500 and 7999;
select max(value) from tablename where value between 7000 and 7499;
select max(value) from tablename where value between 6500 and 6999;
select max(value) from tablename where value between 6000 and 6499;
select max(value) from tablename where value between 5500 and 5999;
select max(value) from tablename where value between 8001 and 8998;

So I can get a list of the max value on each range. I did not found anything similar in any question.

Many thanks

Upvotes: 3

Views: 2808

Answers (3)

fthiella
fthiella

Reputation: 49089

What I would do is to use a query like this:

SELECT
  range1, range2, max(value)
FROM
  (SELECT 5000 AS range1, 5499 AS range2
   UNION ALL SELECT 7500, 7999
   UNION ALL SELECT 7000, 7499
   UNION ALL SELECT 6500, 6999
   UNION ALL SELECT 6000, 6499
   UNION ALL SELECT 5500, 5999
   UNION ALL SELECT 8001, 8998
  ) AS ranges
  LEFT JOIN tablename ON value BETWEEN range1 AND range2
GROUP BY
  range1, range2

of course if ranges are like the ones given you can calculate ranges with a formula, but here you are free to define any range you want. You might also prefer to use a "ranges" table, with proper indexes, to have better performances.

Upvotes: 1

hsz
hsz

Reputation: 152284

Try with:

  SELECT MAX(value)
    FROM tablename
   WHERE value BETWEEN 5000 AND 8998
GROUP BY FLOOR(value / 500)

SQL Fiddle

Upvotes: 4

juergen d
juergen d

Reputation: 204894

select max(case when value between 5000 and 5499 then value else 0 end) as max_5000_5499,
       max(case when value between 7500 and 7999 then value else 0 end) as max_7500_7999
from tablename

Upvotes: 0

Related Questions