How to group records by range in SQL

Question

Hi, I would like to create SQL to group records according to range

For example, suppose that

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 in accordance with the "number" range: [100,200),[200,300),[300,400) and [0,400]. For each range I need the “Price” of the last time “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 build an SQL statement to create it?

I am not working on a specific database. So I'm not looking for any specific SQL SQL statement

Answer 1

Using:

 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 2

Homework?

if, , . .

Answer 3

Although some of the following requirements are outside the scope of the basic SQL standard, they are complete standard SQL functions (for example, row constructors are the full SQL-92 function, CTE are the full SQL-99 function, etc.), and this 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;