How to get the alternative of a 'Common Table Expressions' in Mysql Procedures

Question

I have MSsql Procedure which I want to convert into Mysql Procedure but I don't know how to convert the 'CTE' expression in mysql.

Below I am posting my relevant procedure:

  WITH    CTE
              AS ( SELECT   ROW_NUMBER() OVER ( ORDER BY CASE
                                                          WHEN @SortColumnName = 'UserID'
                                                          AND @SortOrderBy = 'asc'
                                                          THEN UserID
                                                         END ASC, CASE
                                                          WHEN @SortColumnName = 'UserID'
                                                          AND @SortOrderBy = 'desc'
                                                          THEN UserID
                                                          END DESC) AS RN ,
                            UserID ,
                            UserName ,
                            FirstName ,
                            MiddleName ,
                            LastName ,
                            EmailID
                   FROM     [Users]
                 )

This will be easy to port to PostgreSQL, but not to MySQL - it does not support CTE, it does not support `OVER`, it does not support `ROW_NUMBER()`, list goes on... — mvp, Jan 23 '13 at 06:45
@mvp Thnx for ur quick response...But Is there any way to get the desired result for the same logic..in mysql — vikas, Jan 23 '13 at 06:48
[Two](http://stackoverflow.com/questions/5291054/generating-depth-based-tree-from-hierarchical-data-in-mysql-no-ctes) [duplicates](http://stackoverflow.com/questions/1382573/how-do-you-use-the-with-clause-in-mysql) — Bernhard Barker, Jan 23 '13 at 06:58

score 0 · Answer 1 · answered Jan 23 '13 at 07:37

0

You can change it into a subquery, i.e. instead of:

;with cte as
(
    select column
      from table
)
select *
  from cte

you can write:

select *
  from (select column
          from table) as cte

answered Jan 23 '13 at 07:37

Andreas Ågren

3,879
24
33

Thnx but how to put conditions in the expression...Plz help as i am very novice in this field... – vikas Jan 23 '13 at 07:45

score 0 · Accepted Answer · edited May 23 '17 at 12:03

What you need to use is a Derived Table, as you are using the ranking function Row_Number and obviously you would need to refer to it in your WHERE clause now.

MySQL supports derived tables and the performance between CTE and derived tables in SQL is the same, they are both Table Expressions.

Your new code will look something like this: (depending on your need that is not tabled above)

SELECT *
FROM
    (SELECT ROW_NUMBER() OVER (
        ORDER BY 
         CASE WHEN @SortColumnName = 'UserID' AND @SortOrderBy = 'asc' THEN UserID END ASC, 
         CASE WHEN @SortColumnName = 'UserID' AND @SortOrderBy = 'desc' THEN UserID END DESC
    ) AS RN,
      UserID ,
      UserName ,
      FirstName ,
      MiddleName ,
      LastName ,
      EmailID
     FROM     [Users]
 ) DTABLE
WHERE DTABLE.RN = 1;

But this just deals with your question on the CTE.

Row_Number is not available in MySQL as noted above, but check this page for answers on that part: ROW_NUMBER() in MySQL

EDIT: New Example dropping the Row_Number by and making use of LIMIT:

(As stated below, there is a simpler way to accomplish changeble sorting and getting spesific rows, See below query.)

SELECT
      UserID ,
      UserName ,
      FirstName ,
      MiddleName ,
      LastName ,
      EmailID
FROM     
    [Users]
ORDER BY 
         CASE WHEN @SortColumnName = 'UserID' AND @SortOrderBy = 'asc' THEN UserID END ASC, 
         CASE WHEN @SortColumnName = 'UserID' AND @SortOrderBy = 'desc' THEN UserID END DESC END
LIMIT 1,1

Thanx But i am getting error "Script line: 4 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( ORDER BY CASE WHEN SortColumnName = 'UserID' AND SortOrderBy = 'asc' THEN User' at line 17 " while executing — vikas, Jan 23 '13 at 08:03
@vikas This probably has to do with the ranking function. MySQL does not support Row_Number, but there are ways around this. See this page and then alter that bit. http://stackoverflow.com/questions/1895110/row-number-in-mysql — Charl, Jan 23 '13 at 08:08
@vikas I think I am getting what you want to do and there is an easier way: Take out the row number and over by clause completely and then put the case statement in your order by clause and use the limit function to get a spesific row. This way you can still pass in a parameter to specify by which column to order by, and the limit will take out the need to do a row number. I will update my post with example code in a minute or so. — Charl, Jan 23 '13 at 08:29

How to get the alternative of a 'Common Table Expressions' in Mysql Procedures

2 Answers2