T-SQL Row_Number() Over() into MySQL

Asked Jun 01 '16 at 14:51

Active Jun 01 '16 at 15:52

Viewed 91 times

I have already created a T-SQL Stored Procedure and I want to write this same procedure into MySQL.

DELIMITER ;;
CREATE procedure sproc_Test(in _comment text, in _type int, in _createddate datetime, in _fkkey int, in _userid int, in _rowno int)
begin
    set _comment=null;

    insert into tblTest1(comment,type,createddate,fkkey,userid)
    values(_comment,_type,_createddate,_fkkey,_userid);

    if(_type=1)then
        SELECT * FROM
        (SELECT ROW_NUMBER() OVER (ORDER BY tblTest1.id ASC) AS rownumber, tblTest1.*, tblTest2.name as username, tblTest3.name as location
        from tblTest1,tblTest2,tblTest3
        where tblTest1.userid=tblTest2.id and tblTest3.id=tblTest1.fkkey and tblTest1.type=@type) test1
        where test1.rownumber > _rowno and test1.rownumber <=_rowno+5;
    ELSE
        SELECT * FROM tblTest1;
    end if;
end

It gives me error on OVER keyword.

edited Jun 01 '16 at 14:53

Chris Pickford

8,642
5
42
73

asked Jun 01 '16 at 14:51

user3441151

1,880
6
35
79

there is no row_number/over in mysql. it doesn't support windowing functions, so what you want is not possible. – Marc B Jun 01 '16 at 14:53
@MarcB so any other way to achieve this? – user3441151 Jun 01 '16 at 14:54
simulate it manually in client-side code, or build hideously ugly stored procs to do it. – Marc B Jun 01 '16 at 14:55
@MarcB Stored Procedure – user3441151 Jun 01 '16 at 14:56
@MarcB can you change it for MySQL? – user3441151 Jun 01 '16 at 14:58
like I said, you'd have to write one hell of a lot of code to simulate it. – Marc B Jun 01 '16 at 14:59

T-SQL Row_Number() Over() into MySQL

0 Answers0