将ROW_NUMBER限制为对非重复值进行排序

Question

如何对数据进行排名，为每行设置不同的值，但将排名的第一个值提供给不同的行？

我

有电子邮件数据，我必须对电子邮件进行排名，因为它是否有效。我已经完成了验证部分，但是我在排名过程中遇到了很多麻烦。

此数据示例适用于一个人。

ID     | EMAIL             |
181818 | [e-mail]  |
181818 | exam@[e-mail]  |
181818 | [e-mail]  |
181818 |                   |
181818 | [e-mail] |
181818 | [e-mail]  |
181818 |                   |

所以，我的验证让我得到这样的东西

ID     | EMAIL             | VALID
181818 | [e-mail]  |  1
181818 | exam@[e-mail]  |  0
181818 | [e-mail]  |  1
181818 |                   |  0
181818 | [e-mail] |  1
181818 | [e-mail]  |  1
181818 |                   |  0

我对这些数据进行排名的代码是：

SELECT  E.ID,
        UPPER(E.EMAIL),
        ROW_NUMBER()
            OVER (  PARTITION BY E.ID
                    ORDER BY (  CASE
                                  --VALIDATION PROCESS
                                 END) DESC) AS ROWNO
    FROM TABLE E
    WHERE E.ID = 181818 ;

此查询首先返回有效邮件，最后返回无效邮件，这就是我想要的，但是我不希望重复的数据出现在顶部。我想要类似DENSE_RANK但重复的数据在最后是 a 以避免相同的排名值。

返回：

ID     | EMAIL             | ROWNO
181818 | [e-mail]  |  1
181818 | [e-mail]  |  2
181818 | [e-mail] |  3
181818 | [e-mail]  |  4
181818 | exam@[e-mail]  |  5
181818 |                   |  6
181818 |                   |  7

想：

ID     | EMAIL             | ROWNO
181818 | [e-mail]  |  1
181818 | [e-mail] |  2
181818 | [e-mail]  |  3
181818 | [e-mail]  |  4
181818 | exam@[e-mail]  |  5
181818 |                   |  6
181818 |                   |  7

Answer 1

好吧，您可以枚举电子邮件，然后在ORDER BY中使用它。 这会将电子邮件分组，所有值在重复之前只出现一次。

SELECT E.ID, UPPER(E.EMAIL),
       ROW_NUMBER() OVER (PARTITION BY E.ID
                          ORDER BY is_valid DESC, seqnum
                         ) AS rownumber
FROM (SELECT E.*,
             (CASE --VALIDATION PROCESS THEN 1 ELSE 0 END) a is_valid,
             ROW_NUMBER() OVER (PARTITION BY e.ID, UPPER(e.EMAIL) ORDER BY e.id) as seqnum
      FROM TABLE E
     ) E
WHERE E.ID = 181818 ;