CODEWITHSUNDEEP
sql-servert-sql
Brian Ogden
Brian Ogden

Reputation: 19222

Is there a way to replace TOP 100 PERCENT from this Rank() OVER SQL?

So this SQL orders pages in a photo book, it is called after newly uploaded pages are added, it updates the sequence order:

UPDATE Gallery.AlbumAsset
SET
    Sequence = X.Rank
FROM Gallery.AlbumAsset albass
INNER JOIN 
(
    SELECT TOP 100 PERCENT
        Rank() OVER (Order By Sequence, AssetId) As Rank, AssetId
    FROM Gallery.AlbumAsset
    WHERE AlbumId = @AlbumId and Sequence > 0
    ORDER BY Rank

) X ON albass.AssetId = X.AssetId AND albass.Sequence != X.Rank
WHERE albass.AlbumId = @AlbumId

I think the TOP 100 PERCENT is confusing and not optimal for readability, so I attempted the change:

UPDATE Gallery.AlbumAsset
SET
    Sequence = X.Rank
FROM Gallery.AlbumAsset albass
INNER JOIN 
(
    SELECT
        Rank() OVER (Order By Sequence, AssetId) As Rank, AssetId
    FROM Gallery.AlbumAsset
    WHERE AlbumId = @AlbumId and Sequence > 0
    ORDER BY Rank

) X ON albass.AssetId = X.AssetId AND albass.Sequence != X.Rank
WHERE albass.AlbumId = @AlbumId

Which causes the error:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

Perhaps I just need to use TOP but I was trying to simplify this SQL a bit but its been a while since I have worked with "more complex" SQL statements, I almost tried to use CROSS APPLY somehow but I think that is an incorrect choice in direction.

Upvotes: 1

Views: 760

Answers (1)

Nathan Griffiths
Nathan Griffiths

Reputation: 12756

You can remove both the TOP and ORDER BY clauses from the subquery - since this is a subquery, the ORDER BY clause serves no purpose and I suspect the TOP clause is only there to prevent the error you are seeing regarding using ORDER by in a subquery.

Upvotes: 5

Related Questions