CODEWITHSUNDEEP
sqlt-sql
tiermann
tiermann

Reputation: 15

Adding Row numbers to a Stored Procedure

I'm attempting to add row numbers to a stored procedure that was written years ago by someone that has since retired. The two unions seem to where I'm running into trouble. Any suggestions would be helpful as I seemed to be at a dead end here.

CREATE procedure [dbo].[sp_GetReleases]
  @Area VARCHAR(10) = NULL
as

SET @Area = LOWER(@Area)

SELECT
       r.releaseid, r.release_id, r.releasedatetext, r.topic,
       r.releasedate as 'sortdate', '1' as 'sortcode', r.YouTubeID,
       rv.Videoflv, rv.Videomp4      
FROM release r, area a, ReleaseVideos rv
WHERE r.areaid = a.areaid
AND   r.releaseid  = rv.releaseid
AND a.area_id = @area
AND r.webdisplay = 1
AND GETDATE() >= availabledate

UNION

-- This is new 02-19-15
SELECT 
       r.releaseid, r.release_id, r.releasedatetext, r.topic,
       r.releasedate as 'sortdate', '1' as 'sortcode', r.YouTubeID,
       rv.Videoflv, rv.Videomp4      
FROM release r, area a, ReleaseVideos rv, ReleaseStates rs
WHERE   r.releaseid  = rv.releaseid
AND   a.area_id = @area

AND   a.areaid = rs.areaid
AND   r.releaseid = rs.releaseid

AND r.webdisplay = 1
AND GETDATE() >= availabledate

UNION

SELECT 
        r.releaseid, r.release_id, r.releasedatetext, r.topic, 
       (select r2.releasedate
        FROM release r2,  relatedrelease rr2
        WHERE r2.release_id = rr2.release_id) sortdate,
        '2' as 'sortcode',  r.YouTubeID,
        rv.Videoflv, rv.Videomp4
        
FROM release r, relatedrelease rr, ReleaseVideos rv
WHERE r.release_id = rr.related_id
AND   r.releaseid  = rv.releaseid
AND r.webdisplay = 1
AND GETDATE() >= r.availabledate
AND rr.release_id in 
    (SELECT release_id
     FROM release, area a
     WHERE a.area_id = @area
     AND release.areaid = a.areaid)
ORDER BY sortdate DESC, sortcode 
GO

Upvotes: 1

Views: 297

Answers (2)

TT.
TT.

Reputation: 16143

  1. Wrap the whole query as it is now, excluding the ORDER BY clause, in a CTE (named a)
  2. Write a query that selects all columns from a and an additional ROW_NUMBER() based on information in a.

The outline:

WITH a AS (
    -- the original query, minus the order by clause
)
SELECT
    *,
    rn=ROW_NUMBER() OVER (/*your number partitioning/ordering here*/)
FROM
    a
-- original order by clause here

Upvotes: 1

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30663

encapsulate whole sql with another select and apply row_number() for the outer query

select x.*, row_number() over (order by release_id) rn
from (

    SELECT
           r.releaseid, r.release_id, r.releasedatetext, r.topic,
           r.releasedate as 'sortdate', '1' as 'sortcode', r.YouTubeID,
           rv.Videoflv, rv.Videomp4      
    FROM release r, area a, ReleaseVideos rv
    WHERE r.areaid = a.areaid
    AND   r.releaseid  = rv.releaseid
    AND a.area_id = @area
    AND r.webdisplay = 1
    AND GETDATE() >= availabledate
    
    UNION
    
    -- This is new 02-19-15
    SELECT 
           r.releaseid, r.release_id, r.releasedatetext, r.topic,
           r.releasedate as 'sortdate', '1' as 'sortcode', r.YouTubeID,
           rv.Videoflv, rv.Videomp4      
    FROM release r, area a, ReleaseVideos rv, ReleaseStates rs
    WHERE   r.releaseid  = rv.releaseid
    AND   a.area_id = @area
    
    AND   a.areaid = rs.areaid
    AND   r.releaseid = rs.releaseid
    
    AND r.webdisplay = 1
    AND GETDATE() >= availabledate
    
    UNION
    
    SELECT 
            r.releaseid, r.release_id, r.releasedatetext, r.topic, 
           (select r2.releasedate
            FROM release r2,  relatedrelease rr2
            WHERE r2.release_id = rr2.release_id) sortdate,
            '2' as 'sortcode',  r.YouTubeID,
            rv.Videoflv, rv.Videomp4
            
    FROM release r, relatedrelease rr, ReleaseVideos rv
    WHERE r.release_id = rr.related_id
    AND   r.releaseid  = rv.releaseid
    AND r.webdisplay = 1
    AND GETDATE() >= r.availabledate
    AND rr.release_id in 
        (SELECT release_id
         FROM release, area a
         WHERE a.area_id = @area
         AND release.areaid = a.areaid)
    ORDER BY sortdate DESC, sortcode 

) x

Upvotes: 1

Related Questions