Have Geek Brain

Ordering sql query results

My sql query gives columns:

  • product_id

    (which is an integer)
  • pnl

    (which is float - can be negative)

I am getting over 100 lines.

I want to filter the top 40 rows based on abs (pnl). But the results should only be ordered by column pnl, not abs (pnl).

I want to do this for MSSQL 2005.

Is there a way to do this?

+2


a source to share


3 answers


You cannot do it in one step / stat. The selection TOP x

will always be based on the ordering instructions ORDER BY

. You cannot select TOP 40 based on ABS(pnl)

and at the same time order something else.

What you need to do is a two step process - either using a CTE (Common Table Expression) or a temporary table - first select the TOP-40 rows ordered by ABS(pnl)

, then order this result given pnl

.



Sort of:

WITH Top40Rows AS
(
   SELECT TOP 40 product_id, pnl
   ORDER BY ABS(pnl)
)
SELECT product_id, pnl
FROM Top40Rows
ORDER BY pnl
+7


a source


Assuming product_id is not the primary key of the table, you can do something like this:

Select ...
From Table
    Join    (
            Select TOP 40 TablePK
            From Table
            Order by Abs( pnl ) Desc
            ) As Z
        On Table.TablePK = Z.TablePK
Order By Table.pnl ASC

As OMG Ponies mentioned, you can do this as a single view:



Select ...
From (
        Select TOP 40 .....
        From Table
        Order by Abs( pnl ) Desc
        ) As Z
Order By Z.pnl ASC

If you want to use CTE, I would do it with the ROW_NUMBER function:

With RankedItems As
    (
    Select ...
        , ROW_NUMBER() OVER ( ORDER BY Abs(Table.pnl) ) As ItemRank
    From Table
    )
Select 
From RankedItems
Where ItemRank <= 40
Order By pnl ASC
+3


a source


Just for completeness, this is the same as @marc_s, but without using CTEs:

SELECT product_id, pnl FROM (
    SELECT TOP 40 product_id, pnl FROM tbl ORDER BY abs(pnl) ) x 
ORDER BY pnl
0


a source






More articles:


All Articles