Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server select distinct latest values

Tags:

I have a table that has a ton of rows (>10K). Most of the rows have duplicate role values associated with the username.

What I am trying to do is select rows by distinct AND latest role added by request_id. I almost have it, but the part that is kicking my tail is there are null values in some of the request_id fields because those requests were made before that column was added. I STILL need to include them in the select statement in case a user has not entered another request since the update.

Here a example of my table structure:

 id | uname  | role    | request_id
 0  | jsmith  | User   | null
 1  | jsmith  | Admin   | null
 2  | jsmith  | Dude    | null
 3  | jsmith  | Admin   | 56
 4  | jsmith  | Dude    | 56
 5  | jsmith  | Admin   | 57
 6  | jsmith  | Dude    | 57

This would be the desired result:

0  | jsmith  | User    | null
5  | jsmith  | Admin   | 57
6  | jsmith  | Dude    | 57

Here are the statements I've tried so far:

Join

select distinct a.uname, a.role, a.request_id from (
    select * from  das_table 
    ) 
b join das_table a on b.role = a.role and b.request_id = a.request_id
where a.uname = 'jsmith'

Result: This eliminates the rows with request_id = NULL

MAX()

This didn't work for me I guess because MAX() doesn't count null values?

select distinct uname, role, max(request_id) as request_id from das_table where uname='jsmith'
group by uname, role, request_id

Similar questions I've looked at:

One caveat in my question that I think makes it different from the others I've researched is the request_id having the possibility of being null.

Select distinct values from 1 column | SQL Server : select distinct by one column and by another column value | SQL Server select distinct hell