Help with Select - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with Select

Author

Topic

hemulll
Starting Member

9 Posts

Posted - 2007-06-01 : 00:25:30

Hello all,
I have a table with following columns and data:
ID, SrvName, IP, DeviceName
1, Srv1, 10.0.0.1, ETH0
1, Srv1, 10.0.0.2, ETH1
2, Srv2, 10.0.0.3, ETH0 and more ....
so, i want to select SrvName with First and Second IP addresses and not all ip addresses, because sometimes i have up to 6 IpAddresses, or better i want to select 1 column with two firsts Ip seperated by "," if it possible ?

Thanks

----------------------------------------------------------------------------------------
Unix is user friendly , it's just picky about who it's friends are.
----------------------------------------------------------------------------------------

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-01 : 00:29:06

Define two first IP. How do you determine which are the two first IP ?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-01 : 00:29:15

This thread should help: [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53293[/url]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

hemulll
Starting Member

9 Posts

Posted - 2007-06-01 : 00:31:59

khtan: Yes i want to determine which are the two first IP

----------------------------------------------------------------------------------------
Unix is user friendly , it's just picky about who it's friends are.
----------------------------------------------------------------------------------------

hemulll
Starting Member

9 Posts

Posted - 2007-06-01 : 00:59:05

For example: my code is "SELECT ir.ResourceId AS WrkstaId, dt.[Host Name], dt.[IP Address]
FROM dbo.Inv_AeX_AC_TCPIP AS dt INNER JOIN
dbo.ItemResource AS ir ON dt._ResourceGuid = ir.Guid
order by WrkstaId"
and output:
10142 AD-01 10.x.x.1
10142 AD-01 10.x.x.2
10142 AD-01 10.x.x.3
10142 AD-01 10.x.x.4
10142 AD-01 10.x.x.5
10142 AD-01 10.x.x.6
10142 AD-01 10.x.x.7

I want to recive next output:
10142 AD-01 10.x.x.1,10.x.x.2

----------------------------------------------------------------------------------------
Unix is user friendly , it's just picky about who it's friends are.
----------------------------------------------------------------------------------------

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-01 : 01:04:59

see http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx

hemulll
Starting Member

9 Posts

Posted - 2007-06-01 : 01:25:06

I don't understand from this function how can i limit my result
USE pubs
GO

CREATE FUNCTION dbo.ConcatAuthors(@State CHAR(2))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Output VARCHAR(8000)
SET @Output = ''

SELECT @Output = CASE @Output
WHEN '' THEN au_lname
ELSE @Output + ', ' + au_lname
END
FROM Authors
WHERE State = @State
ORDER BY au_lname

RETURN @Output
END

----------------------------------------------------------------------------------------
Unix is user friendly , it's just picky about who it's friends are.
----------------------------------------------------------------------------------------

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-01 : 01:29:49

use TOP 2

hemulll
Starting Member

9 Posts

Posted - 2007-06-01 : 02:25:54

What the syntax of query, something like:
SELECT ir.ResourceId AS WrkstaId, dt.[Host Name], (select top 2 dt.[IP Address])
FROM dbo.Inv_AeX_AC_TCPIP AS dt INNER JOIN
dbo.ItemResource AS ir ON dt._ResourceGuid = ir.Guid
it's same result.
Can you help me to buid this query ?

----------------------------------------------------------------------------------------
Unix is user friendly , it's just picky about who it's friends are.
----------------------------------------------------------------------------------------

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-01 : 02:32:47

i mean use TOP 2 in your concat function


SELECT TOP 2 @Output = CASE @Output
WHEN '' THEN au_lname
ELSE @Output + ', ' + au_lname
END
FROM Authors
WHERE State = @State
ORDER BY au_lname

hemulll
Starting Member

9 Posts

Posted - 2007-06-01 : 04:03:31

khtan:
Thanks , it wotk nice

----------------------------------------------------------------------------------------
Unix is user friendly , it's just picky about who it's friends are.
----------------------------------------------------------------------------------------

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources