Ordering sql query results

My sql query gives the columns:

• product_id (which is an integer)
• pnl (which is float - can be negative)

I get more than 100 rows.

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

I want to do this for MSSQL 2005.

Is there a way to do this?

You cannot do this in a single step / statment. The TOP x selection will always be based on the ORDER BY ordering instruction. You cannot select the TOP 40 based on ABS(pnl) and at the same time order by 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 that result set by pnl.

Something like:

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

Assuming that 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 could do this as a single derived table:

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

If you wanted to use a CTE, then I'd 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

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

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