Search code examples
sql-servert-sqlgroup-bysql-order-by

Group by a set of data and order items in each part separately from other parts in T SQL

I have a set of data that has 2 fields ActivationTime which is a type of DATETIME and Score which is a type of INT.

I want to group all of the data according to these rules:

  • The minimum and maximum activation time values in each group must differ by no more than five days starting from the latest activation time. (In the sample below, the first activation time will be 2017-11-24 09:45:00.000)
  • Grouped data items must be sorted by the score value separately from other grouped data items

Consider the following data as an example:

Score                  ActivationTime
-------------------------------------------
  9               '2017-11-20 09:37:00.000'
  5               '2017-11-21 09:39:00.000'
  4               '2017-11-22 09:40:00.000'
  3               '2017-11-23 09:43:00.000'
  1               '2017-11-24 09:45:00.000'
  12              '2017-11-20 09:49:00.000'
  7               '2017-11-17 09:38:00.000'
  2               '2017-11-15 09:41:00.000'
  0               '2017-11-14 09:44:00.000'
  15              '2017-11-13 09:47:00.000'
  7               '2017-11-12 09:46:00.000'
  20              '2017-11-11 09:42:00.000'
  3               '2017-11-10 09:28:00.000'
  4               '2017-11-09 09:37:57.570'

Then I expect to get the following result:

Score                 ActivationTime             Category
---------------------------------------------------------------
  12             '2017-11-20 09:49:00.000'          1
  9              '2017-11-20 09:37:00.000'          1
  5              '2017-11-21 09:39:00.000'          1
  4              '2017-11-22 09:40:00.000'          1
  3              '2017-11-23 09:43:00.000'          1
  1              '2017-11-24 09:45:00.000'          1

  15             '2017-11-13 09:47:00.000'          2
  7              '2017-11-17 09:38:00.000'          2
  2              '2017-11-15 09:41:00.000'          2
  0              '2017-11-14 09:44:00.000'          2

  20             '2017-11-11 09:42:00.000'          3
  7              '2017-11-12 09:46:00.000'          3
  4              '2017-11-09 09:37:57.570'          3
  3              '2017-11-10 09:28:00.000'          3
Solution

  • From what you've said, it seems that you want something like this. 

    select
Score, ActivationTime, (rnk - 1) / 5 grp
from (
    select
        Score, ActivationTime, dense_rank()over(order by cast(ActivationTime as date) desc) rnk
from
    mytable
) t
order by grp, Score desc

    You should give some sample data and the result you expect to get better help