Solved

Missing numbers found by query in MySQL, some complications

Posted on 2011-03-22
9
401 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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
This video discusses moving either the default database or any database to a new volume.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

757 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

20 Experts available now in Live!

Get 1:1 Help Now