Reputation: 336478
I have a user database where users are assigned an ID number in a certain range according to their type. For example, board members get an ID between 1 and 100, children get an ID between 1001 and 3000, parents get an ID between 3001 and 7000 etc.*
I'd like to get a list of the highest number in use for each "segment" of my IDs.
I can of course get the highest number of all by doing
SELECT MAX(Persons.Number) as Maximum FROM Persons
and get the highest number below 3000 like this:
SELECT MAX(Persons.Number) as MaxChild FROM Persons WHERE Persons.Number<=3000
...but how could I get the highest number below 100 AND the highest number below 1000 AND the highest number below 3000 etc. etc. with a single SELECT
statement?
*I do have these characteristics stored in the database elsewhere; the "bucketing" of ID numbers is just for making it easier to spot at first glance where a certain user belongs
Upvotes: 1
Views: 60
Reputation: 2481
SELECT
MAX(CASE WHEN id BETWEEN 1 and 100 THEN Number ELSE NULL END) as BoardMax,
MAX(CASE WHEN ID BETWEEN 1001 and 3000 THEN Number ELSE null END) as ChildMax,
MAX(CASE WHEN ID BETWEEN 3001 and 7000 THEN Number ELSE null END) as ParentMax
from
Persons
Upvotes: 1
Reputation: 51938
Just use IF()
:
SELECT
MAX(IF(Persons.Number BETWEEN x AND y, Persons.Number, NULL)) AS max_range_x_y,
MAX(IF(Persons.Number BETWEEN i AND j, Persons.Number, NULL)) AS max_range_i_j, ...
FROM Persons;
Above is MySQL syntax. In SQL Server you might use IIF()
instead. What should work in every RDBMS (because it's ANSI-SQL Standard) is
SELECT
MAX(CASE WHEN Persons.Number BETWEEN x AND y THEN Persons.Number ELSE NULL END) AS max_range_x_y,
MAX(CASE WHEN Persons.Number BETWEEN i AND j THEN Persons.Number ELSE NULL END) AS max_range_i_j, ...
FROM Persons;
Upvotes: 2