SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Help


SQL Help

Author
Message
p_shaw
p_shaw
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 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 = 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?
Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15258 Visits: 18607
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
Cool

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;


p_shaw
p_shaw
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 37
Worked great! Thanks
wBob
wBob
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 576
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]
(
rowId INT IDENTITY PRIMARY KEY,
CoverageCode INT 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


Attachments
temp.png (7 views, 15.00 KB)
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16394 Visits: 19554
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 Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
wBob
wBob
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 576
... 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.
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39704 Visits: 38562
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]
(
rowId INT IDENTITY PRIMARY KEY,
CoverageCode INT 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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
wBob
wBob
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 576
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!
Attachments
temp.png (11 views, 15.00 KB)
sunitha.yanagandala
sunitha.yanagandala
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 32
select * from
(
select ROW_NUMBER()over(Partition by coveragecode) rowno,* from Contract
)t
where rowno<=5
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16394 Visits: 19554
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.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search