So, I have a very weird question and don’t know how to frame it well but I’ll to frame it well for everyone to understand.

I have a requirement to get the id value from a table which is like below:

enter image description here

Here PkId is Primary Key, another field Id which can be repeated and 3 more columns. My requirement is to have the output based on the priority of values in:

Column State:
Priority : A > P > V > T > Others

Column Date1 most recent

Column Date2 most recent

This is getting very difficult for me to achieve. Can someone help?

I have tried using CTE suggested by some other posts in StackOverflow which shows the solutions for priority related issues but none seem to help me. I am expecting Id field from the table which I need to later query in another table to see if it exists in the new table.