My SQL and LINQ implementations Row_number () over

Both Oracle and SQL Server have the row_number () over function, which is used primarily for group sorting, but MySQL has no

SELECT * FROM (select Row_number () over (PARTITION by H.alarmindex ORDER by H.handletime DESC) N,               h.* from M_alarmhand LE H) M               WHERE m.n=1

SELECT row_number () over (PARTITION by H.alarmindex ORDER by H.handletime DESC) N, h.* from M_alarmhandle H, this statement is for grouping The sort statement, followed by where M.n=1 is the first row in the group after getting the grouping sorted,

The corresponding my SQL statement is

Select Handlelsh,alarmindex,handlestatus,handletime,handleperson,handledescription,handletype,rank from (select H. *, @rownum: [Email protected]+1, if (@Group =h.alarmindex, @rank: [Email protected]+1, @rank: =1) as rank, @Group: =h. Alarmindex from (SELECT * from M_alarmhandle ORDER by Handletime DESC  ) H, (select @rownum: =0, @Group: = null, @ran k:=0) a) result where rank=1;

The corresponding LINQ statement is

 var m_alarmhandlelist = (from ha in Dbmanager.m_alarmhandle group ha by ha .                                                Alarmindex to HH from MH in HH MH.                                                Handletime Descending Select New                                                    {RowNo = rowno+1, MH. Handlelsh, MH. Handledescription, MH. Handleperson, MH. Handlestatus, MH. Handletime, MH. Handletype, MH. Alarmindex}). Where (m=>m.rowno==1); 

　　

Example of a test function (excerpted from others but already measured)

drop tableifexists wmy;create table wmy (IDint, GroupIdint, salarydecimal(Ten,2) ; insert into wmy values (1,Ten,5500.00),(2,Ten,4500.00),(3, -,1900.00),(4, -,4800.00),(5, +,6500.00),(6, +,14500.00),(7, +,44500.00),(8, -,6500.00),(9, -,7500.00);SelectId,groupid,salary,rank from ( SelectH.id,h.groupid,h.salary, @rownum: [Email protected]+1 , if(@Group =h.groupid, @rank: [Email protected]+1, @rank: =1) asrank, @Group:=H.groupid from ( SelectId,groupid,salary fromwmy ORDER BY GroupId ASC, Salary desc) H, (Select@rownum: =0, @Group: =NULL, @rank: =0) a) result;

　

My SQL and LINQ implementations Row_number () over