CODEWITHSUNDEEP
sqlsql-servert-sql
seph
seph

Reputation: 694

Sort result when using ROW_NUMBER()

This is a follow-up to this question I asked yesterday.
I did not know if I should expand my original question, I decided to start a new one (bear with me...)

My SELECT is like this at the moment:

SELECT *
FROM
(
    SELECT 
        a.guid_column1, b.guidID_column1, c.date_column1, d.guid_column1
        ROW_NUMBER() OVER (PARTITION BY a.guid_column1, b.guid_column1 ORDER BY c.date_column1 DESC) as rn 
    ...
    -- JOINS AND WHERE STUFF HERE
    ....
) t
WHERE t.rn = 1

I get the (expected) result like this:

a.guid_column1   b.guid_column1    c.date_column1    d.guid_column1
-------------------------------------------------------------------
a1               b1                07/08/2013       someUniqueID
a2               b2                05/06/2012       someUniqueID



The tricky part is that I would like to sort that result by a dateadd, something like this ORDER BY dateadd(month, a.float_column, c.date_column1) asc
a.float_column is of course not always the same (and is in fact entered by the user later on).

Is there a way to accomplish this in SQL (I'm using SQL Server 2005)

Upvotes: 0

Views: 130

Answers (2)

Anonymoose
Anonymoose

Reputation: 2471

Below query will calculate the new date in the SELECT-CLAUSE.

If a.float_column is NULL nothing will be added to the original date. 

SELECT dateadd(month, ISNULL(t.float_column,0), t.date_column1) as newDate
    , *
FROM
(
    SELECT 
        a.guid_column1, b.guidID_column1, c.date_column1, d.guid_column1, a.float_column
        ROW_NUMBER() OVER (PARTITION BY a.guid_column1, b.guid_column1 ORDER BY c.date_column1 DESC) as rn 
    ...
    -- JOINS AND WHERE STUFF HERE
    ....
) t
WHERE t.rn = 1

ORDER BY newDate ASC

Upvotes: 2

roman
roman

Reputation: 117380

why don't you just add column into your subquery?

select *
from
(
    select
        a.guid_column1, b.guidID_column1, c.date_column1, d.guid_column1,
        dateadd(month, a.float_column, c.date_column1) as sort_order,
        row_number() over (partition by a.guid_column1, b.guid_column1 order by c.date_column1 desc) as rn 
    --
    -- JOINS AND WHERE STUFF HERE
    --
) t
where t.rn = 1
order by t.sort_order asc

Upvotes: 0

Related Questions