SQL group queries the first few data in each group

  / * First implementation method, low efficiency and error * /DECLARE @DD DATETIME SET @DD = GETDATE () SELECT a.goodsid, A.account, a.lastupdate from                                            Dbo.tb_app_goods a left JOIN dbo.tb_app_goods b on a.account = B.account    and A.lastupdate > B.lastupdate GROUP by A.goodsid, A.account, A.lastupdate have          COUNT (B.GOODSID) < 2 ORDER by A.account, A.lastupdate DESC; PRINT DATEDIFF (Millisecond, @DD, GETDATE ())/ * The second method of implementation, high efficiency and accurate * /SET @DD = GETDATE () SELECT a.goodsid, A.account, a.lastupdate from Dbo.tb_app_goods as a                                Left JOIN (SELECT row_number () over (PARTITION by account ORDER by Lastupdate DESC) as C, * from Dbo.tb_app_goods) as B on a.goodsid = B.goodsid WH          ERE (B.C between 1 and 2) ORDER by A.account, A.lastupdate DESC; PRINT DATEDIFF (Millisecond, @DD, GETDATE ())

Execution Result:

The second method is obviously the most efficient:

Not only is the efficiency high, look closely, the query results are not the same, the first method results more than the second method query:

Reason:

The first method of what ghost, the query came out is not accurate enough,

Summary: Be sure to use the second method

Hereby record that,

SQL group queries the first few data in each group