CODEWITHSUNDEEP
sqlrankingrank
Shoham
Shoham

Reputation: 7294

How can I give rank to dates?

I have an orders table with USER_ID and ORDER_DATE
I want to achieve somthing like this:

SELECT [USER_ID], [FIRST_ORDER_DATE], [SECOND_ORDER_DATE], 
       [THIRD_ORDER_DATE], [FOURTH_ORDER_DATE]
FROM ORDERS

I have tried to create a scalar function (getFirstOrder, getSecondOrder, ...) but after the third its very slow

any ideas?

Thanks!

Upvotes: 1

Views: 102

Answers (1)

user330315
user330315

Reputation:

Something like this: 

select user_id, 
       max(case when rnk = 1 then date_col else null end) as first_order_date,
       max(case when rnk = 2 then date_col else null end) as second_order_date,
       max(case when rnk = 3 then date_col else null end) as third_order_date
from (
     select user_id, 
            date_col,
            dense_rank() over (partition by user_id order by date_col) as rnk
     from orders
) t
group by user_id;

You didn't specify your DBMS. The above is ANSI SQL that works on most modern DBMS.

Upvotes: 3

Related Questions