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:
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
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With