Solved

Missing numbers found by query in MySQL, some complications

Posted on 2011-03-22
9
406 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

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

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

691 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