asked on

Rank Records In The Order They Are Read From The Table

I need to rank records in a table (SC_StatusDaysDate) so they look like attached example. I manually put those rankings in.
I need the records to be ranked in the order that they are read from the table.
The table is in the correct sequence (Order By GROUP_ID, PROBLOG_ID desc) but now need to be ranked the way I need them ranked.
Within a group, I need rankings by SalesID and Status1. If SalesID or Status1 change within a group, increase the ranking by 1.
The rankings will start from 1 again every time the Group_ID changes.

I will write records to this table and they will be in sequence by Group_ID ascending and LogID descending.
Now, I put the ranking numbers into this table manually.
I have no query to do the rankings. I need a query that will read this table and put the rankings in. I cannot figure it out.

The rankings should change anytime the SalesID or Status1 changes inside of a Group_ID.
When the Group_ID changes, the ranking number must start at 1 again. 

Let me know if you need more. Have to step out for a while. 




CREATE TABLE [SC_StatusDaysDate](
       [LogID] [int] NULL,
       [Created_On] [varchar](10) NULL,
       [Group_ID] [int] NULL,
       [SalesID] [varchar](20) NULL,
       [Status1] [varchar](10) NULL,
       [GroupRank] [int] NULL
) ON [PRIMARY]

INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6539123, N'2017-11-02', 2062335, N'MARKAM', N'D', 1)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6539122, N'2017-11-02', 2062335, N'MARKAM', N'D', 1)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6539121, N'2017-11-02', 2062335, N'MARKAM', N'D', 1)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6539120, N'2017-11-02', 2062335, N'MARKAM', N'D', 1)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6539119, N'2017-11-02', 2062335, N'MARKAM', N'D', 1)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6375137, N'2017-10-12', 2062335, N'ARREP', N'C', 2)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6375136, N'2017-10-12', 2062335, N'ARREP', N'C', 2)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6375135, N'2017-10-12', 2062335, N'ARREP', N'C', 2)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6375134, N'2017-10-12', 2062335, N'ARREP', N'C', 2)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6311588, N'2017-09-18', 2062335, N'MARKAM', N'D', 3)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6311587, N'2017-09-18', 2062335, N'MARKAM', N'D', 3)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6311586, N'2017-09-18', 2062335, N'MARKAM', N'D', 3)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6135939, N'2017-07-06', 2062335, N'CSDIS', N'I', 4)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6135938, N'2017-07-06', 2062335, N'CSDIS', N'I', 4)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6468804, N'2017-09-01', 2065114, N'MARKAM', N'C', 1)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6468803, N'2017-09-01', 2065114, N'MARKAM', N'C', 1)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6466186, N'2017-09-01', 2065114, N'MARKAM', N'C', 1)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6466185, N'2017-09-01', 2065114, N'MARKAM', N'C', 1)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6279108, N'2017-05-26', 2065114, N'MARKAM', N'I', 2)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6279107, N'2017-05-26', 2065114, N'MARKAM', N'I', 2)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6279106, N'2017-05-26', 2065114, N'MARKAM', N'I', 2)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6279105, N'2017-05-26', 2065114, N'MARKAM', N'I', 2)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6275635, N'2017-05-26', 2065114, N'MARKAM', N'I', 2)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6275631, N'2017-05-26', 2065114, N'MARKAM', N'I', 2)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6275629, N'2017-05-26', 2065114, N'MARKAM', N'I', 2)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6275628, N'2017-05-26', 2065114, N'MARK', N'I', 3)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6071559, N'2017-05-26', 2065114, N'MARKAM', N'I', 4)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6071556, N'2017-05-26', 2065114, N'MARKAM', N'I', 4)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6071554, N'2017-05-26', 2065114, N'MARKAM', N'I', 4)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6071553, N'2017-05-26', 2065114, N'MARKAM', N'I', 4)

Open in new window

+---------+------------+----------+---------+---------+------+
|  LogID  | Created_On | Group_ID | SalesID | Status1 | Rank |
+---------+------------+----------+---------+---------+------+
| 6539123 | 2017-11-02 |  2062335 | MARKAM  | D       |    1 |
| 6539122 | 2017-11-02 |  2062335 | MARKAM  | D       |    1 |
| 6539121 | 2017-11-02 |  2062335 | MARKAM  | D       |    1 |
| 6539120 | 2017-11-02 |  2062335 | MARKAM  | D       |    1 |
| 6539119 | 2017-11-02 |  2062335 | MARKAM  | D       |    1 |
| 6375137 | 2017-10-12 |  2062335 | ARREP   | C       |    2 |
| 6375136 | 2017-10-12 |  2062335 | ARREP   | C       |    2 |
| 6375135 | 2017-10-12 |  2062335 | ARREP   | C       |    2 |
| 6375134 | 2017-10-12 |  2062335 | ARREP   | C       |    2 |
| 6311588 | 2017-09-18 |  2062335 | MARKAM  | D       |    3 |
| 6311587 | 2017-09-18 |  2062335 | MARKAM  | D       |    3 |
| 6311586 | 2017-09-18 |  2062335 | MARKAM  | D       |    3 |
| 6135939 | 2017-07-06 |  2062335 | CSDIS   | I       |    4 |
| 6135938 | 2017-07-06 |  2062335 | CSDIS   | I       |    4 |
| 6468804 | 2017-09-01 |  2065114 | MARKAM  | C       |    1 |
| 6468803 | 2017-09-01 |  2065114 | MARKAM  | C       |    1 |
| 6466186 | 2017-09-01 |  2065114 | MARKAM  | C       |    1 |
| 6466185 | 2017-09-01 |  2065114 | MARKAM  | C       |    1 |
| 6279108 | 2017-05-26 |  2065114 | MARKAM  | I       |    2 |
| 6279107 | 2017-05-26 |  2065114 | MARKAM  | I       |    2 |
| 6279106 | 2017-05-26 |  2065114 | MARKAM  | I       |    2 |
| 6279105 | 2017-05-26 |  2065114 | MARKAM  | I       |    2 |
| 6275635 | 2017-05-26 |  2065114 | MARKAM  | I       |    2 |
| 6275631 | 2017-05-26 |  2065114 | MARKAM  | I       |    2 |
| 6275629 | 2017-05-26 |  2065114 | MARKAM  | I       |    2 |
| 6275628 | 2017-05-26 |  2065114 | MARK    | I       |    3 |
| 6071559 | 2017-05-26 |  2065114 | MARKAM  | I       |    4 |
| 6071556 | 2017-05-26 |  2065114 | MARKAM  | I       |    4 |
| 6071554 | 2017-05-26 |  2065114 | MARKAM  | I       |    4 |
| 6071553 | 2017-05-26 |  2065114 | MARKAM  | I       |    4 |

Open in new window

RankingExample.xlsx
RankingExplanationAndTableBuild.txt

PortletPaul

Tables are "unordered sets" so despite the facts that you might store rows in a particular order there is no guarantee that any query will read the rows in any particular sequence. You must therefore apply a deliberate order within any query that is dependent on order. Here the change of sales_id can get difficult to detect so I have used 2 apply operators to locate logids when a change occours (oa1) or the minimum login for a "cluster" of rows to be equally ranked. Using these together as a virtual column ensures we have data that changes when the calculated rank needs to change.

select
      LogID
    , Created_On
    , Group_ID
    , SalesID
    , Status1
    , GroupRank
    , calc_rank
from (
    select 
    *
    , dense_rank() over(partition by Group_ID 
                        order by coalesce(changed_at,min_logid) DESC) calc_rank
    from SC_StatusDaysDate sdd
    outer apply (
          select max(sdd2.LogID) changed_at
          from SC_StatusDaysDate sdd2
          where sdd.Group_ID = sdd2.Group_ID
          and sdd.Created_On = sdd2.Created_On
          and sdd.LogID > sdd2.LogID
          and sdd.SalesID <> sdd2.SalesID
          ) oa1
    outer apply (
          select min(sdd2.LogID) min_logid
          from SC_StatusDaysDate sdd2
          where sdd.Group_ID = sdd2.Group_ID
          and sdd.Created_On = sdd2.Created_On
          and sdd.LogID >= sdd2.LogID
          and sdd.SalesID = sdd2.SalesID
          ) oa2
  ) d
;

Open in new window

Result

|   LogID | Created_On | Group_ID | SalesID | Status1 | GroupRank | calc_rank |
|---------|------------|----------|---------|---------|-----------|-----------|
| 6539123 | 2017-11-02 |  2062335 |  MARKAM |       D |         1 |         1 |
| 6539122 | 2017-11-02 |  2062335 |  MARKAM |       D |         1 |         1 |
| 6539121 | 2017-11-02 |  2062335 |  MARKAM |       D |         1 |         1 |
| 6539120 | 2017-11-02 |  2062335 |  MARKAM |       D |         1 |         1 |
| 6539119 | 2017-11-02 |  2062335 |  MARKAM |       D |         1 |         1 |
| 6375137 | 2017-10-12 |  2062335 |   ARREP |       C |         2 |         2 |
| 6375136 | 2017-10-12 |  2062335 |   ARREP |       C |         2 |         2 |
| 6375135 | 2017-10-12 |  2062335 |   ARREP |       C |         2 |         2 |
| 6375134 | 2017-10-12 |  2062335 |   ARREP |       C |         2 |         2 |
| 6311588 | 2017-09-18 |  2062335 |  MARKAM |       D |         3 |         3 |
| 6311587 | 2017-09-18 |  2062335 |  MARKAM |       D |         3 |         3 |
| 6311586 | 2017-09-18 |  2062335 |  MARKAM |       D |         3 |         3 |
| 6135939 | 2017-07-06 |  2062335 |   CSDIS |       I |         4 |         4 |
| 6135938 | 2017-07-06 |  2062335 |   CSDIS |       I |         4 |         4 |
| 6468804 | 2017-09-01 |  2065114 |  MARKAM |       C |         1 |         1 |
| 6468803 | 2017-09-01 |  2065114 |  MARKAM |       C |         1 |         1 |
| 6466186 | 2017-09-01 |  2065114 |  MARKAM |       C |         1 |         1 |
| 6466185 | 2017-09-01 |  2065114 |  MARKAM |       C |         1 |         1 |
| 6279108 | 2017-05-26 |  2065114 |  MARKAM |       I |         2 |         2 |
| 6279107 | 2017-05-26 |  2065114 |  MARKAM |       I |         2 |         2 |
| 6279106 | 2017-05-26 |  2065114 |  MARKAM |       I |         2 |         2 |
| 6279105 | 2017-05-26 |  2065114 |  MARKAM |       I |         2 |         2 |
| 6275635 | 2017-05-26 |  2065114 |  MARKAM |       I |         2 |         2 |
| 6275631 | 2017-05-26 |  2065114 |  MARKAM |       I |         2 |         2 |
| 6275629 | 2017-05-26 |  2065114 |  MARKAM |       I |         2 |         2 |
| 6275628 | 2017-05-26 |  2065114 |    MARK |       I |         3 |         3 |
| 6071559 | 2017-05-26 |  2065114 |  MARKAM |       I |         4 |         4 |
| 6071556 | 2017-05-26 |  2065114 |  MARKAM |       I |         4 |         4 |
| 6071554 | 2017-05-26 |  2065114 |  MARKAM |       I |         4 |         4 |
| 6071553 | 2017-05-26 |  2065114 |  MARKAM |       I |         4 |         4 |

Open in new window

see: http://sqlfiddle.com/#!18/82547/1

thayduck

ASKER

I am sorry, I should have given you better test records. The Created_On dates within a SalesID and Status1 grouping will not always be the same.
I attached a better set of test records. Run your code against these records and you will see that the results are not what I need.
BetterTestRecords.docx

PortletPaul

Instead of attachments would you please paste sample data in a "code block"

CREATE TABLE [SC_StatusDaysDate](
       [LogID] [int] NULL,
       [Created_On] [varchar](10) NULL,
       [Group_ID] [int] NULL,
       [SalesID] [varchar](20) NULL,
       [Status1] [varchar](10) NULL,
       [GroupRank] [int] NULL
) ON [PRIMARY]

INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6539123, N'2017-11-10', 2062335, N'MARKAM', N'D', 1)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6539122, N'2017-11-09', 2062335, N'MARKAM', N'D', 1)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6539121, N'2017-11-09', 2062335, N'MARKAM', N'D', 1)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6539120, N'2017-11-08', 2062335, N'MARKAM', N'D', 1)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6539119, N'2017-11-08', 2062335, N'MARKAM', N'D', 1)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6375137, N'2017-10-12', 2062335, N'ARREP', N'C', 2)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6375136, N'2017-10-12', 2062335, N'ARREP', N'C', 2)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6375135, N'2017-10-11', 2062335, N'ARREP', N'C', 2)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6375134, N'2017-10-10', 2062335, N'ARREP', N'C', 2)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6311588, N'2017-09-18', 2062335, N'MARKAM', N'D', 3)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6311587, N'2017-09-17', 2062335, N'MARKAM', N'D', 3)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6311586, N'2017-09-16', 2062335, N'MARKAM', N'D', 3)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6135939, N'2017-07-06', 2062335, N'CSDIS', N'I', 4)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6135938, N'2017-07-05', 2062335, N'CSDIS', N'I', 4)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6468804, N'2017-09-10', 2065114, N'MARKAM', N'C', 1)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6468803, N'2017-09-09', 2065114, N'MARKAM', N'C', 1)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6466186, N'2017-09-09', 2065114, N'MARKAM', N'C', 1)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6466185, N'2017-09-08', 2065114, N'MARKAM', N'C', 1)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6279108, N'2017-05-26', 2065114, N'MARKAM', N'I', 2)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6279107, N'2017-05-26', 2065114, N'MARKAM', N'I', 2)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6279106, N'2017-05-26', 2065114, N'MARKAM', N'I', 2)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6279105, N'2017-05-25', 2065114, N'MARKAM', N'I', 2)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6275635, N'2017-05-25', 2065114, N'MARKAM', N'I', 2)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6275631, N'2017-05-24', 2065114, N'MARKAM', N'I', 2)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6275629, N'2017-05-23', 2065114, N'MARKAM', N'I', 2)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6275628, N'2017-05-23', 2065114, N'MARK', N'I', 3)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6071559, N'2017-05-22', 2065114, N'MARKAM', N'I', 4)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6071556, N'2017-05-22', 2065114, N'MARKAM', N'I', 4)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6071554, N'2017-05-21', 2065114, N'MARKAM', N'I', 4)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6071553, N'2017-05-21', 2065114, N'MARKAM', N'I', 4)

Open in new window

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

thayduck

ASKER

Thank you very much for your help. The users signed off on the results today. I would have never figured this one out.