CODEWITHSUNDEEP
mysqlsqlsql-server
Abhijeet
Abhijeet

Reputation: 13856

Convert query from MySQL to SQL Server

I am trying to convert below MySQL query to SQL Server.

SELECT
@a:= @a + 1 serial_number,
    a.id,
    a.file_assign_count 
FROM usermaster a,
    workgroup_master b,
    (
        SELECT @a: = 0
    ) AS c
WHERE a.wgroup = b.id
    AND file_assign_count > 0

I understand that := operator in MySQL assigns value to a variable & returns the value immediately. But how can I simulate this behavior in SQL Server?

Upvotes: 1

Views: 181

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562368

SQL Server 2005 and later support the standard ROW_NUMBER() function, so you can do it this way:

SELECT ROW_NUMBER() OVER (ORDER BY xxxxx) AS serial_number,
    a.id,
    a.file_assign_count
FROM usermaster a
JOIN workgroup_master b ON a.wgroup = b.id
WHERE file_assign_count > 0

Re your comments: I edited the above to show the OVER clause. The row-numbering only has any meaning if you define the sort order. Your original query didn't do this, which means it's up to the RDBMS what order the rows are returned in.

But when using ROW_NUMBER() you must be specific. Where I put xxxxx, you would put a column or expression to define the sort order. See explanation and examples in the documentation.

The subquery setting @a:=0 is only for initializing that variable, and it doesn't need to be joined into the query anyway. It's just a style that some developers use. This is not needed in SQL Server, because you don't need the user variable at all when you can use ROW_NUMBER() instead.

If the SQL Server database is returning two rows where your MySQL database returned one row, the data must be different. Because neither ROW_NUMBER() or the MySQL user variables would limit the number of rows returned.

P.S.: please use JOIN syntax. It has been standard since 1992.

Upvotes: 3

Related Questions