MySQL select ranges from query result

Question

For a application I'm currently developing I have a little problem that needs to be fixed, it is oriented for the Dutch so I have a table of all the Dutch zip codes that I can use.

In my application I need to build regions based on cites and this is where I hit a problem, currently I select the first and last known zip code of a city, which is fine in most cases. Which is fine for most cases, but sometimes this doesn't work right because of a zip code being numbered before another city, so that means that in some cases one city matches another.

What I would like to do is get all the ranges with my city from my table. This is a little example what the table looks like (it is missing some unneeded fields for this porpuse).

Zipcode | City --------|------- 2411 AA | Bodegraven 2411 AB | Leiden 2411 AC | Leiden 2411 AD | Bodegraven 2411 AE | Bodegraven 2411 AF | Leiden 2411 AG | Leiden 2411 AH | Bodegraven

With my current query the first zipcode in Bodegraven would be 2411 AA and the last 2411 AH, which isn't interly incorrect, since that is what is stored. However I would like my result to be the following:

Start | End --------|-------- 2411 AA | 2411 AA 2411 AD | 2411 AE 2411 AH | 2411 AH

Is it possible with pure MySQL to generate this result?

Thank you for your time, and responses, if you have any questions please let me know :)

"Is it possible with pure MySQL" I would say no, what you seem to want would be based to row position and there's no row number in mysql — , Sep 09 '17 at 17:55

score 2 · Accepted Answer · answered Sep 09 '17 at 18:08

2

This is very awkward in MySQL, but it is possible.

First we need a way of dynamically defining some kind of "group" identifier that increments as the city changes from row to row. We can do this with MySQL user-defined variables.

SELECT zipcode,
    @groupid:=IF(city=@prev_city,@groupid,@groupid+1) AS groupid,
    @prev_city:=city AS city
FROM MyTable, (SELECT @groupid:=0) AS _init
ORDER BY zipcode;

+---------+---------+------------+
| zipcode | groupid | city       |
+---------+---------+------------+
| 2411 AA | 0       | Bodegraven |
| 2411 AB | 1       | Leiden     |
| 2411 AC | 1       | Leiden     |
| 2411 AD | 2       | Bodegraven |
| 2411 AE | 2       | Bodegraven |
| 2411 AF | 3       | Leiden     |
| 2411 AG | 3       | Leiden     |
| 2411 AH | 4       | Bodegraven |
+---------+---------+------------+

Now we can use this in a derived table subquery and get the MIN() and MAX() value in each group:

SELECT MIN(zipcode) AS Start, MAX(zipcode) AS End
FROM (
    SELECT zipcode,
        @groupid:=IF(city=@prev_city,@groupid,@groupid+1) AS groupid,
        @prev_city:=city AS city
    FROM MyTable, (SELECT @groupid:=0) AS _init
    ORDER BY zipcode
) AS t
WHERE city = 'Bodegraven'
GROUP BY groupid;

+---------+---------+
| Start   | End     |
+---------+---------+
| 2411 AA | 2411 AA |
| 2411 AD | 2411 AE |
| 2411 AH | 2411 AH |
+---------+---------+

Unfortunately, the condition of WHERE city='Bodegraven' must be in the outer query. The subquery must generate the groupings by reading all rows, not just those for Bodegraven. So it must scan through a lot of rows (perhaps you could restrict it to the min and max zipcodes for Bodegraven).

answered Sep 09 '17 at 18:08

Bill Karwin

538,548
86
673
828

Thank you so much, that is exactly what I need :) Running this in my CMD gives the correct result, however going trough my PHP Yii2 PDO connection it doesn't group them from start to end, but just returns every single zipcode as a individual record, is there something I'm missing? My query: SELECT MIN(postcode_id) AS Start, MAX(postcode_id) AS End, groupid FROM ( SELECT postcode_id, @groupid:=IF(city=@prev_city,@groupid,@groupid+1) AS groupid, @prev_city:=city AS city FROM `postcode`, (SELECT @groupid:=0) AS _init ORDER BY postcode_id ) AS t WHERE city = 'Utrecht' GROUP BY groupid; – Bob van Leeuwen Sep 09 '17 at 20:34
Are the postcodes in the original data for Utrecht interleaved with other cities when sorting by postcode, so that there are no sequences of more than one postcode in a row? – Bill Karwin Sep 09 '17 at 20:43
Each postcode is a unique row in the table, and by going trough the table I can see that Utrecht has four or even more cities entered between its first postcode and last postcode on store. Hope this helps. What I do find strange is that the command line seems to return the correct result, where the PHP execution just doesn't group them. – Bob van Leeuwen Sep 09 '17 at 21:07
I've never used Yii2, so is it possible that it's changing the SQL query and stripping out the user-defined variables? I'd verify by enabling the MySQL query log and seeing what it's actually executing. – Bill Karwin Sep 09 '17 at 21:10
I have tried using PDO directly to bypass Yii2 problems, however it still gives the same problems. What I did found out that on PHP PDO @prev_city is NULL, and this is most likely the source of the problem. – Bob van Leeuwen Sep 09 '17 at 21:22
If that were a problem, then the query would have failed in the CLI as well. But you can try initializing it with `... (SELECT @groupid:=0,@prev_city='') AS _init ...` – Bill Karwin Sep 09 '17 at 21:28
Thank you for the support, the above given answer doesn't throw up any errors in the console. This answer however ended fixing it completly: https://stackoverflow.com/questions/24958367/php-pdo-using-mysql-variables. I executed the following query before the main query: `SET @prev_city = '';` – Bob van Leeuwen Sep 09 '17 at 21:31

MySQL select ranges from query result

1 Answers1