?
Solved

Missing numbers found by query in MySQL, some complications

Posted on 2011-03-22
9
Medium Priority
?
407 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 41

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 41

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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
 
LVL 41

Expert Comment

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

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 41

Accepted Solution

by:
Sharath earned 2000 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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

770 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