How to group the records according to the range in SQL

Question

Hi I would like to create a SQL to group the records according to the range

For example, suppose I have

Number    Time            Price
100    20100810           10.0
100    20100812           15.0
160    20100810           10.0
200    20100810           12.0
210    20100811           13.0
300    20100811           14.0
350    20100810           16.0

Now I need to get the records according to the range of the "Number": [100,200),[200,300),[300,400) and [0,400]. For each range, I need the "Price" of the lastest "Time"

So the results should be

NumberRange         Time            Price
    1            20100812           15.0
    2            20100811           13.0
    3            20100811           14.0
    4            20100812           15.0

How can I construct a SQL statement to produce this?

I am not working on a specific Database. So I am looking for no specific database SQL statement

Answer 1

Homework?

You could select an additional column with an if statement to determine what range id relevant. Then group by this new column.

Answer 2

Use:

 SELECT x.rank, x.time, x.price
   FROM (SELECT *,
                CASE 
                   WHEN number BETWEEN 100 and 199 THEN 1
                   WHEN number BETWEEN 200 and 299 THEN 2
                   WHEN number BETWEEN 300 and 399 THEN 3
                   ELSE NULL
                END AS rank
           FROM TABLE) x
           JOIN (SELECT t.rank,
                        MAX(t.time) AS max_time
                   FROM (SELECT *,
                                CASE 
                                  WHEN number BETWEEN 100 and 199 THEN 1
                                  WHEN number BETWEEN 200 and 299 THEN 2
                                  WHEN number BETWEEN 300 and 399 THEN 3
                                  ELSE NULL
                                END AS rank
                           FROM TABLE) t
               GROUP BY t.rank) y ON y.rank = x.rank 
                                 AND y.max_time = x.time
UNION ALL
SELECT x.rank, x.time, x.price
  FROM (SELECT *,
               CASE 
                 WHEN number BETWEEN 0 and 400 THEN 4
                 ELSE NULL
               END AS rank
          FROM TABLE) x
          JOIN (SELECT t.rank,
                       MAX(t.time) AS max_time
                  FROM (SELECT *,
                               CASE  
                                 WHEN number BETWEEN 0 and 400 THEN 4
                                 ELSE NULL
                               END AS rank
                          FROM TABLE) t
              GROUP BY t.rank) y ON y.rank = x.rank 
                                AND y.max_time = x.time

Answer 3

Although some of the following requires fall outside of core Standard SQL, they are full Standard SQL features (e.g. row constructors are a full SQL-92 feature, CTEs are a full SQL-99 feature, etc), and will indeed be found in some products such as SQL Server and Oracle:

WITH MyTable (my_Number, my_Time, Price)
     AS
     (
      SELECT my_Number, CAST(my_Time AS DATE), 
             CAST(Price AS DECIMAL(5, 2))
        FROM (
              VALUES (100, '2010-08-10', 10), 
                     (100, '2010-08-12', 15), 
                     (160, '2010-08-10', 10), 
                     (200, '2010-08-10', 12), 
                     (210, '2010-08-11', 13), 
                     (300, '2010-08-11', 14), 
                     (350, '2010-08-10', 16)
             ) AS MyTable (my_Number, my_Time, Price)
     ), Ranges (NumberRange, range_start, range_end)
     AS 
     (
      SELECT NumberRange, range_start, range_end
        FROM (
              VALUES (1, 100, 200),
                     (2, 200, 300),
                     (3, 300, 400), 
                     (4, 0, 400)
             ) AS Ranges (NumberRange, range_start, range_end)
     ), 
     RangesMaxTimes (NumberRange, range_start, range_end, max_time)
     AS 
     ( 
      SELECT R1.NumberRange, 
             R1.range_start, R1.range_end, 
             MAX(M1.my_Time) AS max_time
        FROM MyTable AS M1
       INNER JOIN Ranges AS R1
          ON R1.range_start <= M1.my_Number
             AND M1.my_Number < R1.range_end
       GROUP 
          BY R1.NumberRange, R1.range_start, R1.range_end
     )
SELECT R1.NumberRange, M1.my_Time, M1.Price
  FROM MyTable AS M1
       INNER JOIN RangesMaxTimes AS R1
          ON R1.range_start <= M1.my_Number
             AND M1.my_Number < R1.range_end
             AND M1.my_Time = R1.max_time;