July 17, 2014 at 12:50 pm
I have a Contract table with a CoverageCode column. I what pull 5 records for each CoverageCode. Currently I am doing this:
SELECT TOP 5 * FROM Contract WHERE CoverageCode = 1
UNION
SELECT TOP 5 * FROM Contract WHERE CoverageCode = 2
etc.....there are over 100 if I go this route
Is there a better way to do this?
July 17, 2014 at 12:55 pm
p_shaw (7/17/2014)
I have a Contract table with a CoverageCode column. I what pull 5 records for each CoverageCode. Currently I am doing this:SELECT TOP 5 * FROM Contract WHERE CoverageCode = 1
UNION
SELECT TOP 5 * FROM Contract WHERE CoverageCode = 2
etc.....there are over 100 if I go this route
Is there a better way to do this?
Suggestion, do something like this
😎
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;
July 17, 2014 at 1:42 pm
Worked great! Thanks
July 21, 2014 at 4:46 am
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
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
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
July 21, 2014 at 6:08 am
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
.
.
.
How does this query compare against Eirikur's? The one you've measured omits a processing step: [Contract] -> [CoverageCodes].
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 21, 2014 at 7:55 am
... if the CoverageCodes are stored in their own table ...
I'm speculating coverageCodes could be a foreign key (as the name would suggest), but of course we don't have this info from the OP. In the event they are not in a separate table and performance of this query was important to them, then they could implement it.
July 21, 2014 at 1:47 pm
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 tempdbGO
-- !!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.
July 21, 2014 at 3:16 pm
Good spot Lynne, that was just in the demo so I could check the results were consistent between the three queries. Sorting is not a requirement from the OP so we can take it out altogether. Guess what, even better results for the FK query, Duration 1, CPU 0 even from a cold cache!
September 3, 2014 at 6:22 am
select * from
(
select ROW_NUMBER()over(Partition by coveragecode) rowno,* from Contract
)t
where rowno<=5
September 3, 2014 at 6:36 am
sunitha.yanagandala (9/3/2014)
select * from(
select ROW_NUMBER()over(Partition by coveragecode) rowno,* from Contract
)t
where rowno<=5
Msg 4112, Level 15, State 1, Line 1
The function 'row_number' must have an OVER clause with ORDER BY.
A version of this model which works was posted by Eirikur, second post on this thread.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply