SQL Help

  • 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?

  • 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;

  • Worked great! Thanks

  • 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

  • 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

  • ... 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.

  • 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.

  • 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!

  • select * from

    (

    select ROW_NUMBER()over(Partition by coveragecode) rowno,* from Contract

    )t

    where rowno<=5

  • 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

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply