wBob (7/21/2014)
I would approach that using TOP and CROSS APPLY, eg:
SELECT c2.*
FROM ( SELECT DISTINCT CoverageCode FROM dbo.[Contract] ) x
CROSS APPLY ( SELECT TOP 5 * FROM dbo.[Contract] c WHERE x.CoverageCode = c.CoverageCode ) c2
If there is an index on CoverageCode, that will really help too, or if the CoverageCodes are stored in their own table ( so we don't have to do the DISTINCT on dbo.Contract ) then that was the most efficient in my simple test rig.
I found this marginally more efficient than the CTE at a scale of 1 million rows, and the CTE appears unable to get an accurate estimated rowcount. This is unlikely to matter at small volumes. but it's always worth considering alternatives : )
My results :
(see attachment)
My simple test rig:
USE tempdb
GO
-- !!WARNING - this is not to be run on a production server
IF OBJECT_ID('dbo.[Contract]') IS NOT NULL DROP TABLE dbo.[Contract]
IF OBJECT_ID('dbo.[CoverageCodes]') IS NOT NULL DROP TABLE dbo.CoverageCodes
GO
CREATE TABLE dbo.[Contract]
(
rowIdINT IDENTITY PRIMARY KEY,
CoverageCodeINT NOT NULL
)
GO
CREATE INDEX _idx ON dbo.[Contract] ( CoverageCode )
GO
;WITH cte AS (
SELECT TOP 1000000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
CROSS JOIN master.sys.columns c2
CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.[Contract] ( CoverageCode )
SELECT rn % 101
FROM cte
GO
SELECT DISTINCT CoverageCode
INTO dbo.CoverageCodes
FROM dbo.[Contract]
GO
CREATE UNIQUE INDEX _idx2 ON dbo.[CoverageCodes] ( CoverageCode )
GO
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO
-- 1)
SELECT c2.*
FROM ( SELECT DISTINCT CoverageCode FROM dbo.[Contract] ) x
CROSS APPLY ( SELECT TOP 5 * FROM dbo.[Contract] c WHERE x.CoverageCode = c.CoverageCode ) c2
ORDER BY 2, 1
GO
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO
-- 2)
;WITH PRE_SEL AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY CoverageCode ORDER BY (SELECT NULL)) AS PRT_RID
,*
FROM Contract
)
SELECT * FROM PRE_SEL WHERE PRT_RID <=5
ORDER BY CoverageCode, rowId
GO
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO
-- 3)
SELECT c2.*
FROM dbo.CoverageCodes x
CROSS APPLY ( SELECT TOP 5 * FROM dbo.[Contract] c WHERE x.CoverageCode = c.CoverageCode ) c2
ORDER BY 2, 1
Two things.
One, I really dislike ORDER BYs that use ordinal positions to identify the column(s) to ORDER BY. It obfuscates something that really shouldn't be. You really should specify the column names explicitly.
Two, iirc, using ordinal position in the ORDE BY clause has been deprecated and may be removed from future versions of SQL Server. Sorry, some one with a little more time on their hands may want to verify this but I do recall reading this several years back. Who knows when they will actually pull it.