CODEWITHSUNDEEP
sqlaggregate-functions
Tim Pietzcker
Tim Pietzcker

Reputation: 336478

Select multiple maximum values from the same column

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

Answers (2)

beejm
beejm

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

fancyPants
fancyPants

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

Related Questions