As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.
Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.
Course Of The Month
10 days, 3 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
MYSQL Group together query
Posted on 2011-03-09
Hi Everyone,
I am trying to put together a query that does the following:
I have a table with 512 entries in it (initially, this will increase with records at we add to it)
This table has columns id, ip_address, in_use where (id is auto_incrementing and primary) and in_use is bool true / false.
Basically I have a list of addresses in this (like I said initially it's got 512)
I need to ask mysql to return rows not in use, but group them together by a set number (ie I need 11 free addresses),
However, this system will have rows marked false on the in_use col right next to others with true.
I need a way to grab a set amount of IP's the id being broken sequentially... example
ids => in_use
1 => false
2 => false
3 => false
4 => false
5 => false
6 => false
7 => false
8 => false
9 => true
10 => true
11 => false
12 => false
13 => false
14 => false
15 => false
16 => false
17 => false
18 => false
19 => false
20 => false
21 => false
22 => false
23 => false
Now if I query for 11 free addresses, closest to the top, it will bring back the list of id's 1 to 12 skipping over 9, this won't work for what I am trying to do :(
What I would like it to do is realize that 9 is in use so cannot use that, thus cannot make a sequential group and pick from 11 to 22... as that is the next available sequential set.
Is there any way to structure a query for this? Is it even possible in MySQL?
Your help is kindly appreciated, Karl.
I am trying to put together a query that does the following:
I have a table with 512 entries in it (initially, this will increase with records at we add to it)
This table has columns id, ip_address, in_use where (id is auto_incrementing and primary) and in_use is bool true / false.
Basically I have a list of addresses in this (like I said initially it's got 512)
I need to ask mysql to return rows not in use, but group them together by a set number (ie I need 11 free addresses),
However, this system will have rows marked false on the in_use col right next to others with true.
I need a way to grab a set amount of IP's the id being broken sequentially... example
ids => in_use
1 => false
2 => false
3 => false
4 => false
5 => false
6 => false
7 => false
8 => false
9 => true
10 => true
11 => false
12 => false
13 => false
14 => false
15 => false
16 => false
17 => false
18 => false
19 => false
20 => false
21 => false
22 => false
23 => false
Now if I query for 11 free addresses, closest to the top, it will bring back the list of id's 1 to 12 skipping over 9, this won't work for what I am trying to do :(
What I would like it to do is realize that 9 is in use so cannot use that, thus cannot make a sequential group and pick from 11 to 22... as that is the next available sequential set.
Is there any way to structure a query for this? Is it even possible in MySQL?
Your help is kindly appreciated, Karl.
[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
2
2-
2
- +1
7 Comments
Active 1 day ago
Without a fully flushed out solution I think I can say it may be possible and get you started.
If you add a rowcount column to the query output and then take the last part of the ip and subtract the rownumber, that should give you a constant number for contiguous ip addresses. Group by that column and add a count to see contiguous blocks. Change the ip to max(ip), ad a min(ip) and you should get start and end and counts of all the blocks. I won't be back at a PC until tomorrow to test all this but I think I will work and someone may be able to write it up before then ifyou can't figure it out.
If you add a rowcount column to the query output and then take the last part of the ip and subtract the rownumber, that should give you a constant number for contiguous ip addresses. Group by that column and add a count to see contiguous blocks. Change the ip to max(ip), ad a min(ip) and you should get start and end and counts of all the blocks. I won't be back at a PC until tomorrow to test all this but I think I will work and someone may be able to write it up before then ifyou can't figure it out.
Thanks Aaron!
I hope to see some type of demonstration of this soon, I am a bit baffled by your terminology so I will await your thoughts
I am doing this currently in the application level, but practically dropping a few thousand rows into array returns is not a good idea....
I look forward to hearing from you!
--Karl.
I hope to see some type of demonstration of this soon, I am a bit baffled by your terminology so I will await your thoughts
I am doing this currently in the application level, but practically dropping a few thousand rows into array returns is not a good idea....
I look forward to hearing from you!
--Karl.
Active 1 day ago
SELECT @rownum:=@rownum+1 rownum, max(ip_address),min(ip_add
FROM (SELECT @rownum:=0) r, tablename
Where in_use = 0
Group by constant
Change the tablename. I this works without tweakin I'll be amazed as I'm typing this only phone.
FROM (SELECT @rownum:=0) r, tablename
Where in_use = 0
Group by constant
Change the tablename. I this works without tweakin I'll be amazed as I'm typing this only phone.
![[Live Webinar] The Cloud Skills Gap](https://filedb.experts-exchange.com/files/public/2017/5/29/3249c5dc-214e-4f8f-afab-ef354630e19f.png)
Active today
Can you try this query? Replace table123 with your actual table.
SELECT t4.*
FROM table123 t4,
(SELECT MIN(ids) ids
FROM (SELECT ids,
IFNULL((SELECT 'no'
FROM table123 t2
WHERE t2.ids BETWEEN t1.ids AND t1.ids + 10
AND in_use = 'true'
LIMIT 1),'yes') newcol
FROM table123 t1) t3
WHERE newcol = 'yes') t5
WHERE t4.ids BETWEEN t5.ids AND t5.ids + 10;
select * from table123;
+------+--------+
| ids | in_use |
+------+--------+
| 1 | false |
| 2 | false |
| 3 | false |
| 4 | false |
| 5 | false |
| 6 | false |
| 7 | false |
| 8 | false |
| 9 | true |
| 10 | true |
| 11 | false |
| 12 | false |
| 13 | false |
| 14 | false |
| 15 | false |
| 16 | false |
| 17 | false |
| 18 | false |
| 19 | false |
| 20 | false |
| 21 | false |
| 22 | false |
| 23 | false |
+------+--------+
23 rows in set (0.00 sec)
SELECT t4.*
FROM table123 t4,
(SELECT MIN(ids) ids
FROM (SELECT ids,
IFNULL((SELECT 'no'
FROM table123 t2
WHERE t2.ids BETWEEN t1.ids AND t1.ids + 10
AND in_use = 'true'
LIMIT 1),'yes') newcol
FROM table123 t1) t3
WHERE newcol = 'yes') t5
WHERE t4.ids BETWEEN t5.ids AND t5.ids + 10;
+------+--------+
| ids | in_use |
+------+--------+
| 11 | false |
| 12 | false |
| 13 | false |
| 14 | false |
| 15 | false |
| 16 | false |
| 17 | false |
| 18 | false |
| 19 | false |
| 20 | false |
| 21 | false |
+------+--------+
11 rows in set (0.02 sec)
If you'll be getting more queries than updates, you might want to consider a caching mechanism:
1. Add column for tracking number of not in use IP addresses from current record until the next IP address not in use. For example:
2. Then, when marking a record with ID x not in use:
Essentially, you would be adjusting the cached count for records with affected counts.
3. When marking a record with ID x as in use:
4. A new record will just have 0 for contiguous_unused if in_use and 1 otherwise.
5. Selecting first set of 11 contiguous records:
This is a rough algorithm. Of course you will have to catch cases when last used or next used record is not existent (start or end of table) and optimize for mass inserts/updates.
Just a possibility.
1. Add column for tracking number of not in use IP addresses from current record until the next IP address not in use. For example:
id in_use contiguous_unused
1 true 1
2 false 0
3 true 1
4 true 2
5 false 0
2. Then, when marking a record with ID x not in use:
# Get first used IP address after current record with ID x.
next_used_id : SELECT MIN(id) FROM ip_addresses WHERE in_use IS TRUE AND id > x;
# Get contiguous_unused for current record.
current_contiguous_unused : SELECT COUNT(*) + 1 FROM ip_addresses WHERE in_use IS FALSE and id > x and id < next_used_id;
# Set contiguous_unused of current record.
UPDATE ip_addresses SET contiguous_unused = current_contiguous_unused WHERE id = x;
# Get last used IP address before current record with ID x.
last_used_id : SELECT MAX(id) FROM ip_addresses WHERE in_use IS TRUE AND id < x;
# Increment contiguous_unused by current_contiguous_unused for all records after last_used_id until previous record.
UPDATE ip_addresses SET contiguous_unused = contiguous_unused + current_contiguous_unused WHERE id > last_used_id AND id < x;
Essentially, you would be adjusting the cached count for records with affected counts.
3. When marking a record with ID x as in use:
# Get last used IP address before current record with ID x.
last_used_id : SELECT MAX(id) FROM ip_addresses WHERE in_use IS TRUE AND id < x;
# Set contiguous_unused of current record.
UPDATE ip_addresses SET contiguous_unused = 0 WHERE id = x;
# Decrement contiguous_unused by 1 for all records after last_used_id until previous record.
UPDATE ip_addresses SET contiguous_unused = contiguous_unused - 1 WHERE id > last_used_id AND id < x;
4. A new record will just have 0 for contiguous_unused if in_use and 1 otherwise.
5. Selecting first set of 11 contiguous records:
SELECT MIN(id) FROM ip_addresses WHERE contiguous_unused > 11;
This is a rough algorithm. Of course you will have to catch cases when last used or next used record is not existent (start or end of table) and optimize for mass inserts/updates.
Just a possibility.
Correction
5. Selecting first set of 11 contiguous records:
You can have flexible queries with this too, if you want to get the smallest unused range or even middle depending on your method for space optimization.
5. Selecting first set of 11 contiguous records:
SELECT MIN(id) FROM ip_addresses WHERE contiguous_unused >= 11;
You can have flexible queries with this too, if you want to get the smallest unused range or even middle depending on your method for space optimization.
Featured Post
Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat. The purpose of this eBook is to educate the reader about ransomware attacks.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL).
Depending on business requirements, a single Stored Procedure can return differe…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month10 days, 3 hours left to enroll
624 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.