SQL query help – max date and order by status exist at first occurrence if not found next value

Need your valuable time and guidance for my sql query(SQL Server)

I have a table as below

ID DATE Status

359 2021-05-01 M 359 2021-05-01 R 359 2021-04-01 M 759 2021-05-01 R 759 2021-04-01 O 123 2021-05-01 M 123 2021-04-01 O 123 2021-03-31 U

and i want the result as below 359 2021-05-01 R 759 2021-05-01 R 123 2021-05-01 M

Date - Max/Latest date Status - First preference should be R(Exit if R found) If R doesn't exist then M If M doesn't exist then O If O doesn't exist then U etc.,

Could you please help me with this?

Thanks in advance Yum

Read more here: https://stackoverflow.com/questions/67507684/sql-query-help-max-date-and-order-by-status-exist-at-first-occurrence-if-not-f

Content Attribution

This content was originally published by Yum.Vee at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: