Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Missing numbers found by query in MySQL, some complications

Posted on 2011-03-22
9
Medium Priority
?
410 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
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

609 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