0

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 |
+-----------+---------+----------+----------+
  • 1
    Please consider reading this advice – mustaccio Sep 6 at 20:24
  • 1
    Please provide DDL (CREATE TABLE blah (field1 type1, .... );) and DML (INSERT INTO blah VALUES (field1_value, .... );) for your sample tables and data - best in the form of a fiddle (dbfiddle.uk / db-fiddle.com). There are a few articles on how to ask questions here on my profile - you might want to have a look? – Vérace Sep 6 at 20:36
  • Absolutely. Sorry yall! My newbie is hanging out here. I will revise this post. – BS Warmer Sep 6 at 20:39
  • Ouch -2... I have altered the question hopefully for the better! Mercy, mercy! – BS Warmer Sep 6 at 21:04
  • See my edit to my answer - both work fine since both have window functions albeit much more mature in PostgreSQL - only arrived in MySQL 8 - I thought it was the result of an "automatic" creation of a table with which you were concerned. – Vérace Sep 6 at 21:17
2

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

  • You are too kind, thank you sir! I will have to play with fiddle, first time using that too. Appreciate the assist for sure! – BS Warmer Sep 6 at 21:18
  • You can always upvote and/or mark my answer as correct for this first "port" of one of your queries! :-) – Vérace Sep 6 at 21:20
  • 1
    Don't you love standards; there are so many! Following a standard is one thing; extending it is, to some extent, the same as violating the standard. How close is SQL Server to any standard (other than its own)? – Rick James Sep 6 at 21:51
  • Who likes click-bait? – Rick James Sep 6 at 21:57
  • @RickJames Standards adherence varies greatly from vendor (anybody who produces a database). I think that PostgreSQL and FirebirdSQL are the closest to the standard. Then maybe Informix? Then Oracle and MS SQL Server and last is MySQL. Check out this site - it's fairly good, but is a bit dated. Of course, compliance can get better over time - it's a difficult area in which to be very dogmatic - intelligent people made various decisions over time. Many comparison sites are rubbish (or [ClickBait](db-engines.com). Still angles, heads of pins &c. – Vérace Sep 6 at 22:10
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.

  • I don't think this answers the OP's question - he wants to know how easy it will be to port MS SQL Server code to MySQL. Does the above construct work with SQL Server? I keep trying to prod the OP towards PostgreSQL but I am also demonstrating that the SQL Server code that he posted can be copied verbatim from one server to another - that won't always be the case obviously. Appeciate your input though - +1! :-) – Vérace Sep 6 at 22:58
  • Yeah, I know his real goal. My example tells him that there is some amount of work to do for this query. It sounds like he has lots more. He's aiming at MySQL 5.6; he would be a lot closer with MySQL 8.0 or, better yet, MariaDB 10.2+. – Rick James Sep 6 at 23:06
  • My ultimate goal is to get a grasp on the magnitude of any switch. I am currently pleased with SQL Server, but internally another group uses MySQL - there is a push to move to a single database. I am trying to understand any major ramifications to this move. This example is simply one query where the ROW_NUMBER)_ over(.... function is utilized. – BS Warmer Sep 7 at 12:16
  • @RickJames - Thank you. Does this script partition by the zipcode? Meaning if we added a second zipcode to the table (assume 3 more rows) - would the result show Row_Rank values of 1,2,3,4,5,6 or 1,2,3,1,2,3? – BS Warmer Sep 7 at 12:19
  • @BSWarmer - That can be done with the aid of a couple more variables. – Rick James Sep 7 at 18:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.