Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 SQL Help Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, July 17, 2014 12:50 PM
 Forum Newbie Group: General Forum Members Last Login: Monday, August 4, 2014 10:55 AM Points: 8, Visits: 37
 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 = 1UNIONSELECT TOP 5 * FROM Contract WHERE CoverageCode = 2etc.....there are over 100 if I go this routeIs there a better way to do this?
Post #1593794
 Posted Thursday, July 17, 2014 12:55 PM
 SSCrazy Group: General Forum Members Last Login: Today @ 4:18 AM Points: 2,560, Visits: 7,192
 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 = 1UNIONSELECT TOP 5 * FROM Contract WHERE CoverageCode = 2etc.....there are over 100 if I go this routeIs 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;`
Post #1593796
 Posted Thursday, July 17, 2014 1:42 PM
 Forum Newbie Group: General Forum Members Last Login: Monday, August 4, 2014 10:55 AM Points: 8, Visits: 37
 Worked great! Thanks
Post #1593816
 Posted Monday, July 21, 2014 4:46 AM
 SSC Rookie Group: General Forum Members Last Login: Tuesday, December 23, 2014 7:21 AM Points: 31, Visits: 520
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 serverIF OBJECT_ID('dbo.[Contract]') IS NOT NULL DROP TABLE dbo.[Contract]IF OBJECT_ID('dbo.[CoverageCodes]') IS NOT NULL DROP TABLE dbo.CoverageCodesGOCREATE TABLE dbo.[Contract](	rowId			INT IDENTITY PRIMARY KEY,	CoverageCode	INT NOT NULL)GOCREATE INDEX _idx ON dbo.[Contract] ( CoverageCode )GO;WITH cte AS (SELECT TOP 1000000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rnFROM master.sys.columns c1	CROSS JOIN master.sys.columns c2	CROSS JOIN master.sys.columns c3)INSERT INTO dbo.[Contract] ( CoverageCode )SELECT rn % 101FROM cteGOSELECT DISTINCT CoverageCodeINTO dbo.CoverageCodesFROM dbo.[Contract]GOCREATE UNIQUE INDEX _idx2 ON dbo.[CoverageCodes] ( CoverageCode )GODBCC DROPCLEANBUFFERSDBCC FREEPROCCACHEGO-- 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 ) c2GODBCC DROPCLEANBUFFERSDBCC FREEPROCCACHEGO-- 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 <=5GODBCC DROPCLEANBUFFERSDBCC FREEPROCCACHEGO-- 3)SELECT c2.*FROM dbo.CoverageCodes x	CROSS APPLY ( SELECT TOP 5 * FROM dbo.[Contract] c WHERE x.CoverageCode = c.CoverageCode ) c2`

Post Attachments
 temp.png (1 view, 15.74 KB)
Post #1594518
 Posted Monday, July 21, 2014 6:08 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 5:05 AM Points: 6,890, Visits: 14,257
 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]. “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 ModenExploring Recursive CTEs by Example Dwain Camps
Post #1594543
 Posted Monday, July 21, 2014 7:55 AM
 SSC Rookie Group: General Forum Members Last Login: Tuesday, December 23, 2014 7:21 AM Points: 31, Visits: 520
 ... 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.
Post #1594602
 Posted Monday, July 21, 2014 1:47 PM
 SSC-Insane Group: General Forum Members Last Login: Yesterday @ 9:08 PM Points: 20,905, Visits: 32,960
 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 serverIF OBJECT_ID('dbo.[Contract]') IS NOT NULL DROP TABLE dbo.[Contract]IF OBJECT_ID('dbo.[CoverageCodes]') IS NOT NULL DROP TABLE dbo.CoverageCodesGOCREATE TABLE dbo.[Contract]( rowId INT IDENTITY PRIMARY KEY, CoverageCode INT NOT NULL)GOCREATE INDEX _idx ON dbo.[Contract] ( CoverageCode )GO;WITH cte AS (SELECT TOP 1000000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rnFROM master.sys.columns c1 CROSS JOIN master.sys.columns c2 CROSS JOIN master.sys.columns c3)INSERT INTO dbo.[Contract] ( CoverageCode )SELECT rn % 101FROM cteGOSELECT DISTINCT CoverageCodeINTO dbo.CoverageCodesFROM dbo.[Contract]GOCREATE UNIQUE INDEX _idx2 ON dbo.[CoverageCodes] ( CoverageCode )GODBCC DROPCLEANBUFFERSDBCC FREEPROCCACHEGO-- 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 ) c2ORDER BY 2, 1GODBCC DROPCLEANBUFFERSDBCC FREEPROCCACHEGO-- 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 <=5ORDER BY CoverageCode, rowIdGODBCC DROPCLEANBUFFERSDBCC FREEPROCCACHEGO-- 3)SELECT c2.*FROM dbo.CoverageCodes x CROSS APPLY ( SELECT TOP 5 * FROM dbo.[Contract] c WHERE x.CoverageCode = c.CoverageCode ) c2ORDER 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.
Post #1594809
 Posted Monday, July 21, 2014 3:16 PM
 SSC Rookie Group: General Forum Members Last Login: Tuesday, December 23, 2014 7:21 AM Points: 31, Visits: 520
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!

Post Attachments
 temp.png (5 views, 15.58 KB)
Post #1594845
 Posted Wednesday, September 3, 2014 6:22 AM
 Grasshopper Group: General Forum Members Last Login: Wednesday, September 17, 2014 4:38 AM Points: 10, Visits: 32
 select * from(select ROW_NUMBER()over(Partition by coveragecode) rowno,* from Contract )twhere rowno<=5
Post #1609982
 Posted Wednesday, September 3, 2014 6:36 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 5:05 AM Points: 6,890, Visits: 14,257
 sunitha.yanagandala (9/3/2014)select * from(select ROW_NUMBER()over(Partition by coveragecode) rowno,* from Contract )twhere rowno<=5`Msg 4112, Level 15, State 1, Line 1The 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. “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 ModenExploring Recursive CTEs by Example Dwain Camps
Post #1609988

 Permissions