August 21, 2015 at 2:04 am
I need to return 1 ip address for each machine. I can easily achieve this with using group by and return max(ipaddress) however I would like to filter out specific range '192.168.%' but only want to do that if machine has more then 1 ip address. If the machine has only 1 ip address then it can return it even if its '192.168.%'
below is test table to show sample data
CREATE TABLE dbo.[testip](
[machineid] INT NULL,
[ipaddress] [varchar](20) NULL
)
GO
INSERT INTO [dbo].[testip] ([machineid],[ipaddress]) VALUES (1,'155.119.1.22')
INSERT INTO [dbo].[testip] ([machineid],[ipaddress]) VALUES (1,'192.168.1.5')
INSERT INTO [dbo].[testip] ([machineid],[ipaddress]) VALUES (2,'192.168.1.6')
INSERT INTO [dbo].[testip] ([machineid],[ipaddress]) VALUES (3,'155.119.1.34')
INSERT INTO [dbo].[testip] ([machineid],[ipaddress]) VALUES (4,'155.119.1.44')
INSERT INTO [dbo].[testip] ([machineid],[ipaddress]) VALUES (4,'192.168.1.16')
INSERT INTO [dbo].[testip] ([machineid],[ipaddress]) VALUES (4,'192.168.1.26')
select * from testip
Result
machineid ipaddress
1 155.119.1.22
1 192.168.1.5
2 192.168.1.6
3 155.119.1.34
4 155.119.1.44
4 192.168.1.16
4 192.168.1.26
select machineid, max(ipaddress) from testip where ipaddress not like '192.168.%'
Result (This would not show machineid 2)
machineid ipaddress
1 155.119.1.22
3 155.119.1.34
4 155.119.1.44
Any suggestions would be welcome. Thx.
August 21, 2015 at 2:25 am
denis.gendera (8/21/2015)
I need to return 1 ip address for each machine. I can easily achieve this with using group by and return max(ipaddress) however I would like to filter out specific range '192.168.%' but only want to do that if machine has more then 1 ip address. If the machine has only 1 ip address then it can return it even if its '192.168.%'below is test table to show sample data
CREATE TABLE dbo.[testip](
[machineid] INT NULL,
[ipaddress] [varchar](20) NULL
)
GO
INSERT INTO [dbo].[testip] ([machineid],[ipaddress]) VALUES (1,'155.119.1.22')
INSERT INTO [dbo].[testip] ([machineid],[ipaddress]) VALUES (1,'192.168.1.5')
INSERT INTO [dbo].[testip] ([machineid],[ipaddress]) VALUES (2,'192.168.1.6')
INSERT INTO [dbo].[testip] ([machineid],[ipaddress]) VALUES (3,'155.119.1.34')
INSERT INTO [dbo].[testip] ([machineid],[ipaddress]) VALUES (4,'155.119.1.44')
INSERT INTO [dbo].[testip] ([machineid],[ipaddress]) VALUES (4,'192.168.1.16')
INSERT INTO [dbo].[testip] ([machineid],[ipaddress]) VALUES (4,'192.168.1.26')
select * from testip
Result
machineid ipaddress
1 155.119.1.22
1 192.168.1.5
2 192.168.1.6
3 155.119.1.34
4 155.119.1.44
4 192.168.1.16
4 192.168.1.26
select machineid, max(ipaddress) from testip where ipaddress not like '192.168.%'
Result (This would not show machineid 2)
machineid ipaddress
1 155.119.1.22
3 155.119.1.34
4 155.119.1.44
Any suggestions would be welcome. Thx.
If the machine has only 1 ip address then it can return it even if its '192.168.%'.
and
Result (This would not show machineid 2).
appear to contradict each other. Machine ID 2 has only 1 IP address, so it should be included, according to your description.
Which way should it be?
August 21, 2015 at 2:34 am
Hi,
the filter criteria should be following maybe it was not clear in my question
Machine has 1 IP Address ==> return that address even if it's '192.168.%'
Machine has > 1 IP Address ==> return address that but not the '192.168.%'
In my example the final result should be
machineid ipaddress
1 155.119.1.22
2 192.168.1.6
3 155.119.1.34
4 155.119.1.44
August 21, 2015 at 2:55 am
I'm sure there are other ways to solve this, but this is what immediately sprang to mind:
WITH CTE AS (
SELECT machineid,
COUNT(machineid) AS IPTotal
FROM testip
GROUP BY machineid
)
SELECT t.machineid,
MAX(t.ipaddress)
FROM testip t
INNER JOIN cte c ON c.machineid = t.machineid
WHERE (c.IPTotal = 1)
OR (c.IPTotal > 1 AND t.ipaddress NOT LIKE '192.168.%')
GROUP BY t.machineid
ORDER BY t.machineid;
August 21, 2015 at 4:20 am
hi,
that is what I wanted, thx very much for quick response, appreciated.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply