Rewrite subquery

  • HI friends,

    Is there any way to rewrite this query from performance point of view - 

    DECLARE @var1 as DECIMAL(10,3)

    SELECT @var1 = (( SELECT TOP 1 col1 FROM ( SELECT TOP 50 PERCENT col1 

    FROM ( SELECT col1, col2,

    ROW_NUMBER() OVER (Partition BY col2 ORDER BY col1 ASC)as RASC,

    ROW_NUMBER() OVER (Partition BY col2 ORDER BY col1 DESC)as RDESC

    FROM #temp



    ORDER BY col1)AS A

    ORDER BY col1 DESC

    Any help would be appreciated.

    many thanks in advance

    To get quick answer follow this link:

  • The first thing I notice is that you are calculating two ROW_NUMBERs that you never use.

    You also have unbalanced parentheses.

    If you want any more than that, I suggest that you follow the advice that you've included a link to in your signature. For performance questions, it also helps to include the actual query plan.


    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Are you trying to get the median?

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 3 posts - 1 through 3 (of 3 total)

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