I have a group of rows that grouped by host and user. I want to return the first row of each group of hosts. I have struggled with this all day and need some help. With this T-SQL I have built the table:
USE Test
GO
IF OBJECT_ID(N'[dbo].Sample') IS NOT NULL
DROP TABLE [dbo].Sample
GO
-- Create the Sample table
CREATE TABLE [dbo].Sample(
ID [int] IDENTITY NOT NULL,
Host [varchar](128) NOT NULL,
Username [varchar](128) NOT NULL,
Seconds [int] NOT NULL)
GO
SET NOCOUNT ON
INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C001','Smith',120012);
INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C001','Jone',100012);
INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C001','Doe',90012);
INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C001','Cole',80012);
INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C001','Lee',70012);
INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C002','Lee',8012);
INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C002','Cole',5012);
INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C002','Smith',4012);
INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C003','Doe',90012);
INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C003','Lee',80012);
INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C003','Cole',7012);
INSERT INTO [dbo].Sample(Host,Username,Seconds) VALUES('C003','Smith',60012);
SET NOCOUNT OFF
I want to write a select statement that will return the first row of each host group:
C001 Smith 120012
C002 Lee 8012
C003 Doe 90012
Thanks for pointing me in the right direction.
Ken
Give a look to the ROW_NUMBER() function in books online; partition by host and order by seconds.|||Try:
select
a.*
from
dbo.Sample as a
where
not exists(
select
*
from
dbo.Sample as b
where
b.Host = a.Host
and b.[ID] < a.[ID]
)
go
-- 2005
;with cte
as
(
select
Host, Username, Seconds,
row_number() over(partition by Host order by [ID]) as rn
from
dbo.Sample
)
select
*
from
cte
where
rn = 1
go
AMB
|||Also,
select
a.*
from
dbo.Sample as a
where
a.[ID] = (
select
min(b.[ID])
from
dbo.Sample as b
where
b.Host = a.Host
)
go
AMB
