How to return the first row of each group of rows

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