Display DISTINCT value on SQL statement by column condition-CodePudding

i'm introducing you the problem with DISTINCT values by column condition i have dealt with and can't provide any idea how i can solve it.

So. The problem is i have two Stephen here declared , but i don't want duplicates: ** The problem: **

id  vehicle_id  worker_id    user_type   user_fullname
9   1              NULL         external_users     John Dalton   
10  1               16          employees          Mike            
11  1                1          employees          Stephen         
12  2               173         employee           Nicholas        
13  2                1          employee           Stephen         
14  1              NULL         external_users     Peter      
**

The desired output:**

id  vehicle_id  worker_id    user_type   user_fullname
9   1              NULL         external_users     John Dalton   
10  1               16          employees          Mike                 
12  2               173         employee           Nicholas        
13  2                1          employee           Stephen         
14  1              NULL         external_users     Peter

I have tried CASE statements but without success. When i group by it by worker_id, it removes another duplicates, so i figured out it needs to be grouped by some special condition? If anyone can provide me some hint how i can solve this problem , i will be very grateful.

Thank's!

CodePudding user response：

There are no duplicate rows in this table. Just because Stephen appears twice doesn't make them duplicates because the ID, VEHICLE_ID, and USER_TYPE are different.

What you need to do is decide how you want to identify the Stephen record you wish to see in the output. Is it the one with the highest VEHICLE_ID? The "latest" record, i.e. the one with the highest ID?

You will use that rule in a window function to order the rows within your criteria, and then use that row number to filter down to the results you want. Something like this:

select id, vehicle_id, worker_id, user_type, user_fullname
from (
    select id, vehicle_id, worker_id, user_type, user_fullname,
        row_number() over (partition by worker_id, user_fullname order by id desc) n
    from user_vehicle
) t
where t.n = 1