Solved

Missing numbers found by query in MySQL, some complications

Posted on 2011-03-22
9
402 Views
Last Modified: 2012-05-11
This is my database table ("table_number"):

id      Number      Name       Town
25          1           James       New York
57          1           Doris         Boston
78          2           Dave         Boston
89          7           Chuck        Boston
126        2           Bertie        New York
153        3           Marion       New York
198        5           Veronica    Boston
234        6           Allen          Boston

This is how I'd like it print
id      Number      Name      Town: Boston
                       
57          1           Doris         Boston
78          2           Dave         Boston
198        5           Veronica    Boston
234        6           Allen          Boston
89          7           Chuck        Boston

id      Number      Name      Town: New York

25          1           James       New York
126        2           Bertie        New York
153        3           Marion       New York

I got help from experts at EE with the code. I am not very fluent in SQL.
Since I have several identical Numbers, the code doesn't work correctly. It also seems like it is not possible to distinguish between Town = Boston and Town = New York. If I could do so, the problem is actually solved, I guess.
Please could someone of you Gurus help me?


SELECT MIN(rownum) min_Missing_Number 
  FROM (  SELECT id, 
                 Number, 
                 Name, 
                 Town,
                 @rownum := @rownum + 1 AS rownum 
            FROM table_number, 
                 (SELECT @rownum := 0) r 
        ORDER BY Number) t1 
 WHERE Number <> rownum AND Name = '$name' AND Town = '$town' AND id <=500;";

Open in new window

0
Comment
Question by:lericson
  • 5
  • 4
9 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 35194997
If you want the result as:
id      Number      Name      Town: Boston
                       
57          1           Doris         Boston
78          2           Dave         Boston
198        5           Veronica    Boston
234        6           Allen          Boston
89          7           Chuck        Boston

Then try this query.
SELECT t1.* 
  FROM table_number1 AS t1 
       JOIN (  SELECT Number, 
                      MAX(Id) AS ID 
                 FROM table_number
             GROUP BY Number) AS t2 
         ON t1.Number = t2.Number 
            AND t1.ID = t2.ID;

Open in new window

0
 
LVL 40

Expert Comment

by:Sharath
ID: 35195000
Typo in table name.
SELECT t1.* 
  FROM table_number AS t1 
       JOIN (  SELECT Number, 
                      MAX(Id) AS ID 
                 FROM table_number
             GROUP BY Number) AS t2 
         ON t1.Number = t2.Number 
            AND t1.ID = t2.ID;

Open in new window

0
 

Author Comment

by:lericson
ID: 35195480
Sharath_123,
You helped me out the other day and given the prerequisites I gave then, your solution was correct. In this question I have added Town. Town is an important variable since it separates the two Number series. I believe Town is a more important variable than is ID. Would you be kind enogh to consider it for a moment.
0
 

Author Comment

by:lericson
ID: 35195515
Sharath_123,

My question might not be as clear as it should have been. Let me add I want to find "the missing"number(s) in the Number series. After I have added Town to it, and I need to group or order by town, I don't know how to change the code you gave me the other day. That is a more clear statement and the question becomes How can I find the lowest missing number , if any, in each of the two series, the series for Boston and the series for New York?.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 40

Expert Comment

by:Sharath
ID: 35195769
ok, what is your expected result?
0
 
LVL 40

Expert Comment

by:Sharath
ID: 35195774
Do you want the least missing number for each town?
0
 

Author Comment

by:lericson
ID: 35197286
Yes. That's exactly what I want.
0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 35199611
There is no missing number for New York, Hence this query gives you Boston and missing number as 3.
SELECT Town, 
         MIN(RowNum) AS MissingNumber 
    FROM (  SELECT *, 
                   IF(@Town = Town,@rownum := @rownum + 1,@rownum := 1) AS RowNum, 
                   IF(@Town <> Town,@Town := Town,'ABC')                s 
              FROM Table_Number, 
                   (SELECT @rownum := 1, 
                           @Town := 'ABC') r 
          ORDER BY Town, 
                   Number) t1 
   WHERE Number <> RowNum 
GROUP BY Town;

Open in new window

Here is the sample I tested.
SELECT * FROM Table_Number1 order by Town,Number;

+------+--------+----------+----------+
| id   | Number | Name     | Town     |
+------+--------+----------+----------+
|   57 |      1 | Doris    | Boston   |
|   78 |      2 | Dave     | Boston   |
|  198 |      5 | Veronica | Boston   |
|  234 |      6 | Allen    | Boston   |
|   89 |      7 | Chuck    | Boston   |
|   25 |      1 | James    | New York |
|  126 |      2 | Bertie   | New York |
|  153 |      3 | Marion   | New York |
+------+--------+----------+----------+
8 rows in set (0.00 sec)
SELECT Town, 
         MIN(RowNum) AS MissingNumber 
    FROM (  SELECT *, 
                   IF(@Town = Town,@rownum := @rownum + 1,@rownum := 1) AS RowNum, 
                   IF(@Town <> Town,@Town := Town,'ABC')                s 
              FROM Table_Number1, 
                   (SELECT @rownum := 1, 
                           @Town := 'ABC') r 
          ORDER BY Town, 
                   Number) t1 
   WHERE Number <> RowNum 
GROUP BY Town;
+--------+---------------+
| Town   | MissingNumber |
+--------+---------------+
| Boston |             3 |
+--------+---------------+
1 row in set (0.04 sec)

Open in new window

0
 

Author Closing Comment

by:lericson
ID: 35202949
You are my hero. Many thanks.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi (http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump) had suggested a “sed” way, I actually shell …
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now