Equivalent of ROW_NUMBER() in MySQL

Question

I am trying to convert the following SQL query into MySQL but wondering if there is a equivalent of ROW_NUMBER() in MySQL.

 SELECT TOP (@NumResults) * FROM (   
    SELECT 
      ROW_NUMBER() OVER (
        ORDER BY 
          CASE WHEN @OrderBy = 'PRODUCT' AND @OrderByDirection = 'D' THEN product_name END DESC,
          CASE WHEN @OrderBy = 'PRODUCT' AND @OrderByDirection = 'A' THEN product_name END,
          CASE WHEN @OrderBy = 'CATEGORY' AND @OrderByDirection = 'D' THEN category_name END DESC,
          CASE WHEN @OrderBy = 'CATEGORY' AND @OrderByDirection = 'A' THEN category_name END,
          CASE WHEN @OrderBy = 'MANUFACTURER' AND @OrderByDirection = 'D' THEN manufacturer_name END DESC,
          CASE WHEN @OrderBy = 'MANUFACTURER' AND @OrderByDirection = 'A' THEN manufacturer_name END, product_name 
      )AS RowNumber, *
    FROM #TempDataFeedTable
    WHERE manufacturer_name like @ManufacturerName
    ) _temp 
  WHERE RowNumber > (@Index - 1)

Should I use self-join ? I've already Googled and looked at all other solutions in stack overflow, but since my sql query has multiple select statements I am having trouble getting the correct result. Can anyone help ?

This is what I've tried so far, but I don't get same results as that of SQL database.

SET @row_number:=0;
SELECT * FROM ( 
  SELECT   (@row_number:=@row_number + 1) As RowNumber, importeddatafeedid, product_name, categoryid,manufacturerid, image_url, link_url, upc, mpn, description, msrp, manufacturer_name FROM    
TempDataFeedTable 
    WHERE manufacturer_name like p_ManufacturerName 
        ORDER BY 
          CASE WHEN p_OrderBy = 'PRODUCT' AND p_OrderByDirection = 'D' THEN product_name END DESC,
          CASE WHEN p_OrderBy = 'PRODUCT' AND p_OrderByDirection = 'A' THEN product_name END,
          CASE WHEN p_OrderBy = 'CATEGORY' AND p_OrderByDirection = 'D' THEN category_name END DESC,
          CASE WHEN p_OrderBy = 'CATEGORY' AND p_OrderByDirection = 'A' THEN category_name END,
          CASE WHEN p_OrderBy = 'MANUFACTURER' AND p_OrderByDirection = 'D' THEN manufacturer_name END DESC,
          CASE WHEN p_OrderBy = 'MANUFACTURER' AND p_OrderByDirection = 'A' THEN manufacturer_name END, product_name 

)
_temp 
  WHERE RowNumber > (p_Index - 1)
 LIMIT 0,10;

Google. http://stackoverflow.com/questions/1895110/row-number-in-mysql — Denziloe, Mar 08 '17 at 17:21
I voted to close this as a duplicate of a 2009 question, because MySQL still doesn't support windowing functions, and the workarounds are described well in that other question. For what it's worth, windowing functions are currently in development for MySQL, but there is no ETA. See this Feb. 2017 presentation: https://www.slideshare.net/DagHWanvik/sql-window-functions-for-mysql — Bill Karwin, Apr 23 '17 at 02:32

Equivalent of ROW_NUMBER() in MySQL

0 Answers0