Help with getting or setting row number in table
-
New to SQL Query,
I have a table with list of ip addresses and other data. I want a list that only shows 1 ip that falls into the range of the subnet that is dispayed. IE I have 20 listings of 192.168.20.###. In the list I only want one these displayed so that I can see a unique list of subnets. I can't figure out how to compare the ip in the current row with the ip from the next row. there seems to be no way to say goto row 33 and get that ip address. i want to step through each row and compare it to the next row without changing to that row if possible. Sorry if this makes no sense, it sure doesn't make any sense to me either! Thanks for any help in advance!
-
Can you post some sample data with an explanation of what your list should look like?
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I store IP addresses as four tinyint columns so things like this are easy and no check contraints are required.
Perhaps something like this:
SELECT MIN(IPAddress)
FROM YourTable
GROUP BY LEFT(IPAddress,LEN(IPAddress)-CHARINDEX('.',REVERSE(IPAddress)))--Jonathan
--Jonathan -
I am bound to the ip in one column due to a program we run that uses this table
here is sample of what I have
table workstations
ws_id, ip_address, user_name, etc...
1, 192.168.10.10, john,
2, 192.168.10.25, henry,
3, 192.168.10.101, sue,
4, 192.168.10.251, harry,
5, 192.168.12.34, mindy,
6, 192.168.12.45, joe,
7, 192.168.12.100 admin,
I am trying to get a unique list of subnets based here on the third octet of these ip's. each time we initialize the db for the program we use it destroys all the data in another table that assigns a group name based on this octet number. We have to type it in based on the ip address. i created a table of group names and ip's. I will use this table to fill in the group name table that the program uses. i want the list of unique ip's so that I can create a fill in file the user can adjust and update my group ip table with querys
thanks for the replies
-
SELECT DISTINCT PARSENAME(ip_address,2)
FROM Workstations--Jonathan
--Jonathan -
Thanks Jonathan, you put me on the right track, i ended up doing a distinct select into a temporary table and then another distinct select on that table to finally get the result set. the parsename function descibed in help says it returns object type information, how did you know it would work on a string, also can you do more than 4 splits
-
quote:
Thanks Jonathan, you put me on the right track, i ended up doing a distinct select into a temporary table and then another distinct select on that table to finally get the result set. the parsename function descibed in help says it returns object type information, how did you know it would work on a string, also can you do more than 4 splits
I guess I still don't understand what result set you're looking for, but I'm sure there's no need for a temporary table.
PARSENAME() takes up to four sysname (nvarchar(128)) pieces separated by periods(BOL is wrong on this) and an int between one and four as its parameters. No reason it won't work with any string that meets this requirement.
--Jonathan
--Jonathan -
Jonathan:
Thanx, I never have thought of using PARSENAME on my IPs 😉 I like to still have things to learn 😛 It makes waking up in the morning almost worth while 😉
Regards, Hans!
-
Just a question from the curious..
You talk about subnets, but nowhere do I see the netmask? Are you assuming default subnetmasks always?
If not, then you must also pair each ip with the correct netmask in order to be able to place it into the correct subnet.
Like Jonathan, I too am confused as to what actual problem you want to solve..
=;o)
/Kenneth
-
quote:
Just a question from the curious..You talk about subnets, but nowhere do I see the netmask? Are you assuming default subnetmasks always?
If not, then you must also pair each ip with the correct netmask in order to be able to place it into the correct subnet.
Like Jonathan, I too am confused as to what actual problem you want to solve..
=;o)
/Kenneth
Welcome on board! Looking forward for your advices
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
quote:
quote:
Just a question from the curious..You talk about subnets, but nowhere do I see the netmask? Are you assuming default subnetmasks always?
If not, then you must also pair each ip with the correct netmask in order to be able to place it into the correct subnet.
Like Jonathan, I too am confused as to what actual problem you want to solve..
=;o)
/Kenneth
Welcome on board! Looking forward for your advices
Frank
Sorry about the late reply,
My system at home went on the blink and I have been out of commission a couple of weeks.
I am not worried about hosts, the front end GUI takes care of that.
We use surf control for web site monitoring. The person that uses the program has to enter the group names, our names for the different subnets we have on campus, which are many, by the third octet of our ip address ranges. When the data is gathered and imported into a new sql db, the group names do not get updated. I am filling in the table with the group_id and group_name based on the IP address in the table for the workstations. I break the IP addresses down to external and internal addresses. I then break down each host ip address for internal addresses based on that third octet. Once I massage the group names he can run his reports for the host name and page and etc and the group names are already in place.
By the way, thanks for all the help from everyone, I found it quite refreshing to talk with people that know their business.
I posted another ?, and look forward to the answers I get.
Thanks again
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply