Using distinct , Case and order by in one query

Question

I have a requirements to retrieve users mobile numbers from the database without duplicates so i'm using distinct key word to do that.

Also not all mobile numbers are well formatted so I'm using Case to format the numbers.

Lastly I want to get the latest 500 mobile numbers so I wanted to order it by Order by ID desc but it shows

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

below is my query

 select distinct top 500  case 

                     when len(PhoneNumber) = 13 And phoneNumber  like '+311%' then replace(PhoneNumber, '+311', '0')
                     when len(PhoneNumber) = 13 And phoneNumber  like '311-%' then replace(PhoneNumber, '311-', '0')

                     when len(PhoneNumber) = 13 And phoneNumber  like '0311%' then stuff(PhoneNumber, 1, 4 , 0 )

                     when len(PhoneNumber) = 14 And phoneNumber  like '00311%'then stuff(PhoneNumber, 1, 5 ,'0')
                     when len(PhoneNumber) = 14 And phoneNumber  like '00311%' then stuff(PhoneNumber, 1, 4 ,'0')


                     when len(PhoneNumber) = 12 And phoneNumber  like '311%' then stuff(PhoneNumber, 1 , 3 , '0')
                     when len(PhoneNumber) = 12 then REPLACE (PhoneNumber, '-', '')

                     when len(PhoneNumber) = 11 And phoneNumber  like '00%' then Stuff(PhoneNumber, 1, 2 , '0')
                     else PhoneNumber
                end 


from users where  cityid = 1 and statusid = 1  and  (len(PhoneNumber) >= 10 And len(PhoneNumber) <= 14) 
and PhoneNumber not like '"%' and PhoneNumber not like '+311-%' and PhoneNumber not like '01%'
and PhoneNumber  like '0%'

Order by id 

UPDATE

As Metal suggested , I'm using row_number() and partition by it gives me one mobile number , Which is not correct result

select top 500000 Mobile
from 
    (select 
        row_number() over (partition by PhoneNumber order by id desc) rn,
        case 
        when len(PhoneNumber) = 13 And phoneNumber  like '+966%' then replace(PhoneNumber, '+966', '0')
        when len(PhoneNumber) = 13 And phoneNumber  like '966-%' then replace(PhoneNumber, '966-', '0')
        when len(PhoneNumber) = 13 And phoneNumber  like '0966%' then stuff(PhoneNumber, 1, 4 , 0 )
        when len(PhoneNumber) = 14 And phoneNumber  like '00966%'then stuff(PhoneNumber, 1, 5 ,'0')
        when len(PhoneNumber) = 14 And phoneNumber  like '0096%' then stuff(PhoneNumber, 1, 4 ,'0')
        when len(PhoneNumber) = 12 And phoneNumber  like '966%' then stuff(PhoneNumber, 1 , 3 , '0')
        when len(PhoneNumber) = 12 then REPLACE (PhoneNumber, '-', '')
        when len(PhoneNumber) = 11 And phoneNumber  like '00%' then Stuff(PhoneNumber, 1, 2 , '0')
        else PhoneNumber
        end  as Mobile
    from consigneeDetail 
    where  cityid = 1 and statusid = 1  and  (len(PhoneNumber) >= 10 And len(PhoneNumber) <= 14) 
            and PhoneNumber not like '"%' and PhoneNumber not like '+966-%' and PhoneNumber not like '01%'
            and PhoneNumber  like '0%') t1
where rn = 1

Answer 1

SELECT DISTINCT phonenumber 
FROM   (SELECT id, 
               CASE 
                 WHEN Len(phonenumber) = 13 
                      AND phonenumber LIKE '+311%' THEN 
                 REPLACE(phonenumber, '+311', '0') 
                 WHEN Len(phonenumber) = 13 
                      AND phonenumber LIKE '311-%' THEN 
                 REPLACE(phonenumber, '311-', '0') 
                 WHEN Len(phonenumber) = 13 
                      AND phonenumber LIKE '0311%' THEN 
                 Stuff(phonenumber, 1, 4, 0) 
                 WHEN Len(phonenumber) = 14 
                      AND phonenumber LIKE '00311%'THEN 
                 Stuff(phonenumber, 1, 5, '0') 
                 WHEN Len(phonenumber) = 14 
                      AND phonenumber LIKE '00311%' THEN 
                 Stuff(phonenumber, 1, 4, '0') 
                 WHEN Len(phonenumber) = 12 
                      AND phonenumber LIKE '311%' THEN 
                 Stuff(phonenumber, 1, 3, '0') 
                 WHEN Len(phonenumber) = 12 THEN REPLACE (phonenumber, '-', '') 
                 WHEN Len(phonenumber) = 11 
                      AND phonenumber LIKE '00%' THEN 
                 Stuff(phonenumber, 1, 2, '0') 
                 ELSE phonenumber 
               end AS phonenumber 
        FROM   users 
        WHERE  cityid = 1 
               AND statusid = 1 
               AND ( Len(phonenumber) >= 10 
                     AND Len(phonenumber) <= 14 ) 
               AND phonenumber NOT LIKE '"%' 
               AND phonenumber NOT LIKE '+311-%' 
               AND phonenumber NOT LIKE '01%' 
               AND phonenumber LIKE '0%' 
        ORDER  BY id) 
LIMIT  500 

Answer 2

use subquery instead, then row_number() to get distinct values.

select top 500 t2.col1 from (
    select row_number() over (partition by t1.col1 order by id desc) rn, t1.col1
    from 
        (select         
            id,
            case 
            when len(PhoneNumber) = 13 And phoneNumber  like '+311%' then replace(PhoneNumber, '+311', '0')
            when len(PhoneNumber) = 13 And phoneNumber  like '311-%' then replace(PhoneNumber, '311-', '0')
            when len(PhoneNumber) = 13 And phoneNumber  like '0311%' then stuff(PhoneNumber, 1, 4 , 0 )
            when len(PhoneNumber) = 14 And phoneNumber  like '00311%'then stuff(PhoneNumber, 1, 5 ,'0')
            when len(PhoneNumber) = 14 And phoneNumber  like '00311%' then stuff(PhoneNumber, 1, 4 ,'0')
            when len(PhoneNumber) = 12 And phoneNumber  like '311%' then stuff(PhoneNumber, 1 , 3 , '0')
            when len(PhoneNumber) = 12 then REPLACE (PhoneNumber, '-', '')
            when len(PhoneNumber) = 11 And phoneNumber  like '00%' then Stuff(PhoneNumber, 1, 2 , '0')
            else PhoneNumber
            end  as col1
        from users 
        where  cityid = 1 and statusid = 1  and  (len(PhoneNumber) >= 10 And len(PhoneNumber) <= 14) 
                and PhoneNumber not like '"%' and PhoneNumber not like '+311-%' and PhoneNumber not like '01%'
                and PhoneNumber  like '0%') t1
    ) t2 
where t2.rn = 1