returning grouped records

[ter79]

Here is the DDL that creates the table and some dummy data:

CREATE TABLE [dbo].[Error](
[ErrorID] [int] IDENTITY(1,1) NOT NULL,
[CompanyID] [int] NULL,
[LayoutID] [int] NULL,
[PeriodID] [int] NULL,
[FileID] [int] NULL,
[ProductID] [int] NULL,
[TreatyID] [int] NULL,
[TempErrorTypeID] [int] NULL,

CONSTRAINT [Error_PK] PRIMARY KEY CLUSTERED
(
[ErrorID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


insert into Error(CompanyID,LayoutID,PeriodID,FileID,TreatyID,TempErrorTypeID)
values (1,1,1,1,1,1)
insert into Error(CompanyID,LayoutID,PeriodID,FileID,TreatyID,TempErrorTypeID)
values (1,1,1,1,1,1)
insert into Error(CompanyID,LayoutID,PeriodID,FileID,TreatyID,TempErrorTypeID)
values (1,1,1,1,1,1)
insert into Error(CompanyID,LayoutID,PeriodID,FileID,TreatyID,TempErrorTypeID)
values (1,1,1,1,1,1)
insert into Error(CompanyID,LayoutID,PeriodID,FileID,TreatyID,TempErrorTypeID)
values (1,1,1,1,1,1)
insert into Error(CompanyID,LayoutID,PeriodID,FileID,TreatyID,TempErrorTypeID)
values (1,1,1,1,1,1)

insert into Error(CompanyID,LayoutID,PeriodID,FileID,TreatyID,TempErrorTypeID)
values (2,2,2,2,2,2)
insert into Error(CompanyID,LayoutID,PeriodID,FileID,TreatyID,TempErrorTypeID)
values (2,2,2,2,2,2)
insert into Error(CompanyID,LayoutID,PeriodID,FileID,TreatyID,TempErrorTypeID)
values (2,2,2,2,2,2)


insert into Error(CompanyID,LayoutID,PeriodID,FileID,TreatyID,TempErrorTypeID)
values (2,1,2,2,2,2)
insert into Error(CompanyID,LayoutID,PeriodID,FileID,TreatyID,TempErrorTypeID)
values (2,1,2,2,2,2)
insert into Error(CompanyID,LayoutID,PeriodID,FileID,TreatyID,TempErrorTypeID)
values (2,1,2,2,2,2)


insert into Error(CompanyID,LayoutID,PeriodID,FileID,TreatyID,TempErrorTypeID)
values (2,2,null,2,2,2)
insert into Error(CompanyID,LayoutID,PeriodID,FileID,TreatyID,TempErrorTypeID)
values (2,2,null,2,2,2)
insert into Error(CompanyID,LayoutID,PeriodID,FileID,TreatyID,TempErrorTypeID)
values (2,2,null,2,2,2)


insert into Error(CompanyID,LayoutID,PeriodID,FileID,TreatyID,TempErrorTypeID)
values (3,3,3,3,3,3)
insert into Error(CompanyID,LayoutID,PeriodID,FileID,TreatyID,TempErrorTypeID)
values (3,3,3,3,3,3)

insert into Error(CompanyID,LayoutID,PeriodID,FileID,TreatyID,TempErrorTypeID)
values (4,4,4,4,4,4)
select e.CompanyID, LayoutID, PeriodID, TempErrorTypeID from Error e
group by e.CompanyID, LayoutID, PeriodID, TempErrorTypeID

Count CompanyID LayoutID PeriodID TempErrorTypeID
6 1 1 1 1
3 2 1 2 2
3 2 2 Null 2
3 2 2 2 2
2 3 3 3 3
1 4 4 4 4


The issue is that when the count of the returned records is over, in this case, 5. I need to only return the top 5 records, with no order just whatever the first five pulled from the table and if the count is less than 5, return all the records.
However, I also need to return the other fields, without affecting the grouping, in the table and they will tie into other tables to “convert” the id field into a description.

 

[markros]

What is your SQL Server database version?

This is very easy in SQL Server 2005 and up

select * from (select myFields, row_number() over (partition by CompanyID order by LayoutID) as rowNum from Errors) E where RowNum <=5