how do I change this query from SQL Server 2016 to MySQL? 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 |
+-----------+---------+----------+----------+
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 &lt;= 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 &lt;= 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