Manipulating SQL query to create data categories that return maximum values

Question

I have a table that looks like this at the moment:

Day  Limit  Price  
1   52      0.3  
1   4       70  
1   44      200  
1   9       0.01   
1   0       0.03  
1   0       0.03  
2   52      0.4  
2   10      70  
2   44      200      
2   5       0.01   
2   0       0.55   
2   2       50

Is there a way I can use SQL to manipulate the result into a table with different categories for price and selecting the maximum value for the limit respective to its price?

Day   0-10   10-100     100+  
 1     52       4        44  
 2     52      10        44

Answer 1

You can use CASE and MAX:

SELECT Day,
  MAX(CASE WHEN Price BETWEEN 0 AND 10 THEN Limit ELSE 0 END) as ZeroToTen,
  MAX(CASE WHEN Price BETWEEN 10 AND 100 THEN Limit ELSE 0 END) as TenToHundred,
  MAX(CASE WHEN Price > 100 THEN Limit ELSE 0 END) as HundredPlus
FROM YourTable
GROUP BY Day

Here is the Fiddle.

BTW -- if you're using MySQL, add ticks around LIMIT since it's a keyword.

Good luck.