how do I change this query from SQL Server 2016 to MySQL?

All we need is an easy explanation of the problem, so here it is.

This is a sample of a query that I currently use regularly in SQL Server 2016 (Standard edition). I need assistance converting this script to MySQL 5.6 syntax that will provide the same result.

Create the table:

CREATE TABLE zip_unique
(     store_nbr integer
     ,zipcode varchar(30)
     ,distance float);

Populate the table:

INSERT INTO zip_unique (store_nbr, zipcode, distance)
VALUES (123, '76135', 2.356)
     , (456, '76135', 3.002)
     , (789, '76135', 9.521)

SQL Server Query to Rank Zipcode based on the distance value:

SELECT store_nbr
      ,zipcode
      ,distance
      ,ROW_NUMBER() OVER(PARTITION BY ZIPCODE
                             ORDER BY DISTANCE) AS ROW_RANK
  FROM zip_unique

Output:

+-----------+---------+----------+----------+
| store_nbr | zipcode | distance | ROW_RANK |
+-----------+---------+----------+----------+
|       123 |   76135 |    2.356 |        1 |
|       456 |   76135 |    3.002 |        2 |
|       789 |   76135 |    9.521 |        3 |
+-----------+---------+----------+----------+

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You can do the following:

(See also the PostgreSQL fiddle and MySQL one with your data)

CREATE TABLE zip_unique
(     
  store_nbr integer
  ,zipcode varchar(30)
  ,distance float
);

Then populate:

INSERT INTO zip_unique (store_nbr, zipcode, distance)
VALUES (123, '76135', 2.356)
     , (456, '76135', 3.002)
     , (789, '76135', 9.521);

And then perform the query:

SELECT store_nbr
      ,zipcode
      ,distance
      ,ROW_NUMBER() OVER(PARTITION BY ZIPCODE
                             ORDER BY DISTANCE) AS ROW_RANK
  FROM zip_unique;

Result:

store_nbr   zipcode     distance    row_rank
      123     76135        2.356           1
      456     76135        3.002           2
      789     76135        9.521           3

At @Rick_James ‘ suggestion, I also ran this:

SELECT store_nbr
      ,zipcode
      ,distance
      ,ROW_NUMBER() OVER(PARTITION BY ZIPCODE
                             ORDER BY DISTANCE) AS ROW_RANK
      ,DENSE_RANK() OVER(PARTITION BY ZIPCODE
                             ORDER BY DISTANCE) AS D_RANK
      ,RANK()       OVER(PARTITION BY ZIPCODE
                             ORDER BY DISTANCE) AS R_RANK
  FROM zip_unique;

Result

store_nbr   zipcode     distance    row_rank    d_rank  r_rank
123     76135   2.356   1   1   1
456     76135   3.002   2   2   2
789     76135   9.521   3   3   3

See the PostgreSQL fiddle here and the MySQL one here.

The ranks will vary according to the data – in this case they’re the same. This was just to show that PostgreSQL will perform standard Window function code, as does MySQL (PostgreSQL is still the superior db though!).

One of the things I didn’t mention previously was PostgreSQL’s almost fanatical pursuit of the standards laid down by the ISO/ANSII committee on SQL.


The following is the answer to the original question – see edits to trace what’s gone on prior to the above answer.

You can do the following, (see the MySQL fiddle here and the PostgreSQL one here):

CREATE TABLE fred (mary int, paul varchar(20));

Populate it:

INSERT INTO fred VALUES (1, 'sdf'), (2, 'xxx'), (3, 'yyy');

MySQL syntax seems to require this – CTAS, i.e. `CREATE TABLE AS which more or less does what you want:

CREATE TABLE bill AS SELECT * FROM fred WHERE mary <= 2;

Then

SELECT * FROM bill;

Result:

mary    paul
   1     sdf
   2     xxx

PostgreSQL does it this way (only differences shown):

SELECT * INTO TABLE bill FROM fred WHERE mary <= 2

The result is the same.

So, it appears, at least as far as I have been able to determine, that PostgreSQL does exactly what you want. MySQL will do the same thing, but it requires a slight (but hardly crippling) change to the syntax of the query. You will see from the fiddles that both syntaxes are accepted in PostgreSQL

Method 2

I asked for it, so I had better answer it…

Based on the data from Vérace, the following should apply to any MySQL >= 4.1 and any MariaDB:

SELECT store_nbr,
       zipcode,
       distance,
       @rank := @rank + 1  AS RowRank   -- kludge for numbering the rows
    FROM zip_unique
    JOIN ( SELECT @rank := 0 ) AS init  -- initialization for the kludge
    ORDER BY distance;

However, it may not apply to whatever comes after MySQL 8.0 since it sets an @variable in a SELECT.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply