The latest record of each group after the MSSQL group is taken

Database in two tables, the user table and the Bonus record table, the bonus record table in a user has more than one message, there is an effective time, now to query:

Bonus is valid three days before each user takes the latest bonus record and the user is locked

The previous method was written directly in the C # code:

For (all end-locked users)

Query the bonus record table top 1 where uid=??? ORDER BY effective Time DESC

If (the record queried above takes effect three days ago)

Output

Today, I spent most of my time researching and finally finding a SQL statement to find out the results:

That row_number function in MSSQL2008 above will have!!!

The latest record of each group after the MSSQL group is taken