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

How to retrieve Top 3 records in the group level

Hi all,

I have a report which is grouped by a field called R_ID, which gives me a list of records for each R_ID. So here is the thing, I want to get only top 3 records for each R_ID. Is there any way to do this thing in the report level. I tried it from the query level, but the result is not like what I wanted.

Please let me know if some body has any idea.

Thx.

Doing this in a report wouldn′t be the best way as you would get all the data from the server and then only would display a part of it. better filter the data on the server and send only back the appropiate results, then you would have no problem on the client / reporting service. Anway, if you want to do this, you might have a look at the RowNumber() property in Reporting Services. You could set the Visibility of the row to Visible=True if the RowNumber("GroupName") equals or is less than 3 =IIF(Rownumber("GroupName") <= 3;True;False)

Doing this on the server depends on which server version you are using. SQL Server 2005 probably would enable you to use ROW_NUMBER() on the server side. SQL Server 2k does not implement this new function, so you would have to use something else instead.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

How to retrieve primary key columns from db?

Hello all. I need to extract the column names that form the primary key group on a table in SQL Server. I have a table called Account and it contains ten columns. The primary key consists of two columns - MasterAccountID, AccountID. This primary key is a unique constraint and is clustered (it acts as an index as well as a primary key group). I have tried the following to no avail:

exec sp_pkeys Account -> returns no rows

exec sp_helpindex Account -> throws an error stating that the object 'Account' does not exist

If I run the following SQL statement, I can see all of the PK_* constraints in the database, so I know they are there:

select * from information_schema.table_constraints
where constraInt_type IN ('PRIMARY KEY','FOREIGN KEY')

Again, I need to be able to specify a table name and have it return the columns (don't care if it returns extra fields) that make up the primary key fields for that table. Thanks!

Perhaps something like this:

select

*fromsyscolumnswhere id=object_id('yourTable')and colidin(select colidfromsysindexkeyswhere id=object_id('yourTable'))

|||

declare

@.TableNamevarchar(255)

set

@.TableName='tblPersons'

SELECT

CCU.*

FROM

INFORMATION_SCHEMA.Table_Constraints TCJOININFORMATION_SCHEMA.Constraint_Column_Usage CCUON CCU.CONSTRAINT_NAME= TC.CONSTRAINT_NAME

WHERE

CONSTRAINT_TYPE

='PRIMARY KEY'AND TC.TABLE_NAME= @.TableName

but this also works: look at the quotes ''

execsp_pkeys'tblPersons'