Restrict ROW_NUMBER to order non-repeated values

Question

How can I rank my data setting different values to each row, but giving the first values of the rank to the distinct rows?

I have emails data and I have to RANK the email as it is valid or not. I already did the validation part, however I'm having a lot of trouble with the ranking process.

This example of data is for one person.

ID     | EMAIL             |
181818 | example@mail.com  |
181818 | exam@pe@mail.com  |
181818 | example@mail.com  |
181818 |                   |
181818 | example1@mail.com |
181818 | examlpe@mail.com  |
181818 |                   |

So, my validation gets me something like this

ID     | EMAIL             | VALID
181818 | example@mail.com  |  1
181818 | exam@pe@mail.com  |  0
181818 | example@mail.com  |  1
181818 |                   |  0
181818 | example1@mail.com |  1
181818 | examlpe@mail.com  |  1
181818 |                   |  0

My code to rank this data is:

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 ;

This query returns the valid mails first and the invalid last, which is what I want, however I dont want repeated data to appear at top. I would like something like DENSE_RANK but the repeated data to be a at the end in order to avoid equal ranking values.

RETURN:

ID     | EMAIL             | ROWNO
181818 | example@mail.com  |  1
181818 | example@mail.com  |  2
181818 | example1@mail.com |  3
181818 | examlpe@mail.com  |  4
181818 | exam@pe@mail.com  |  5
181818 |                   |  6
181818 |                   |  7

WANTED:

ID     | EMAIL             | ROWNO
181818 | example@mail.com  |  1
181818 | example1@mail.com |  2
181818 | examlpe@mail.com  |  3
181818 | example@mail.com  |  4
181818 | exam@pe@mail.com  |  5
181818 |                   |  6
181818 |                   |  7

Answer 1

Well, you can enumerate the emails and then use that in the ORDER BY. That will put the emails into groups, with all values appearing exactly once before duplicates.

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 ;