First off, apologies if this has been asked elsewhere as I was unable to find any solution. The best I get is retrieving latest 1 record or 2-3 records. I'm more in search of all records (the number could be dynamic, could be 1 or 2 or maybe 50 ) based on recent Datetime value. Well so basically here is the problem,

I have a table as follows,

What I am looking to do is, write up a query which will give me all the recent data based on Datetime, so in this case, I should get the following result,

The RowId shows (as the name suggests) gives the number of Rows for a particular Datetime block. This will not always be 4, it's dynamic based on the data received so could be 1,2,4 or even 50 ...

Hope I was able to convey the question properly, Thank you all for reading and Pre-Thank you to those who provide solution to this.

CodePudding user response：

you can use window function row_number to find out the latest entry for each projectid:

select * from (
   select * , row_number() over (partition by projectid order by APILoadDatetime desc) rn
   from tablename
) t where rn = 1

CodePudding user response：

select top 1 with ties * 
from tablename
order by row_number() 
over (partition by projectid order by APILoadDatetime desc) ;