Q:

SQL query for Group By as inner query

I have a table like below
TestDetails
Submitted_on                      Subject    Submitted_By
3/4/2020 7:45:26 PM               English       Shyam
3/4/2020 9:45:26 PM               English       Rahul
3/5/2020 9:45:26 PM               Arabic        Rahul
3/4/2020 8:45:26 PM               French        Yash
3/4/2020 7:45:26 PM               Spanish       John
3/4/2020 6:45:26 PM               Spanish       John

I want to select latest submission for each subject from above table.
The output should be : 
 Submitted_on                     Subject    Submitted_By
3/4/2020 9:45:26 PM               English       Rahul
3/5/2020 9:45:26 PM               Arabic        Rahul
3/4/2020 8:45:26 PM               French        Yash
3/4/2020 7:45:26 PM               Spanish       John

I have tried like below
But getting the output as
 Submitted_on                    Subject    Submitted_By
3/4/2020 7:45:26 PM               English       Shyam
3/4/2020 9:45:26 PM               English       Rahul
3/5/2020 9:45:26 PM               Arabic        Rahul
3/4/2020 8:45:26 PM               French        Yash
3/4/2020 7:45:26 PM               Spanish       John

SELECT MAX(Submitted_on),Subject,Submitted_By FROM TestDetails GROUP BY Subject,Submitted_By

A:

Find the below query
;WITH CTE AS
(
    SELECT ROW_NUMBER() OVER (PARTITION BY subject ORDER BY subject DESC) duplicates, * 
    FROM TestDetails
)
SELECT submitted_on, subject, submitted_by FROM CTE WHERE duplicates = 1

