Help with getting or setting row number in table

Question

Post reply

Help with getting or setting row number in table

terry ball

SSChasing Mays

Points: 635
More actions
November 24, 2003 at 8:24 am

#81545

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!

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Can you post some sample data with an explanation of what your list should look like?

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Jonathan SSC-Insane Points: 20427 More actions · Answer 2

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

terry ball SSChasing Mays Points: 635 More actions · Answer 3

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

Jonathan SSC-Insane Points: 20427 More actions · Answer 4



SELECT DISTINCT PARSENAME(ip_address,2)

FROM Workstations

--Jonathan

terry ball SSChasing Mays Points: 635 More actions · Answer 5

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

Jonathan SSC-Insane Points: 20427 More actions · Answer 6

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

Hans Lindgren SSChampion Points: 10496 More actions · Answer 7

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!

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 8

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 9

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

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

terry ball SSChasing Mays Points: 635 More actions · Answer 10

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
http://www.insidesql.de
http://www.familienzirkus.de

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