Ranking Dates
-
Hello
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!
-
Something like this?
WITH RankedORDERS AS (
SELECT USER_ID,ORDER_DATE,
ROW_NUMBER() OVER(PARTITION BY USER_ID ORDER BY ORDER_DATE) AS rn
FROM ORDERS)
SELECT USER_ID,
MAX(CASE WHEN rn=1 THEN ORDER_DATE END) AS FIRST_ORDER_DATE,
MAX(CASE WHEN rn=2 THEN ORDER_DATE END) AS SECOND_ORDER_DATE,
MAX(CASE WHEN rn=3 THEN ORDER_DATE END) AS THIRD_ORDER_DATE,
MAX(CASE WHEN rn=4 THEN ORDER_DATE END) AS FOURTH_ORDER_DATE
FROM RankedORDERS
GROUP BY USER_ID
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
This should do it - You'll need to add extra order dates as required.
--======= TEST DATA ==========
if object_id('tempdb..#ORDERS') is not null
drop table #ORDERS;
create table #ORDERS
(
UserID int,
OrderDate datetime
);
insert into #ORDERS values ( 522, '5 Jun 2012' );
insert into #ORDERS values ( 522, '7 Jun 2012' );
insert into #ORDERS values ( 522, '10 Jun 2012' );
insert into #ORDERS values ( 522, '11 Jun 2012' );
insert into #ORDERS values ( 607, '8 Jun 2012' );
insert into #ORDERS values ( 607, '13 Jun 2012' );
insert into #ORDERS values ( 607, '20 Jun 2012' );
--========= SOLUTION =============
select UserId,
first_order_date=MAX(case when rowid=1 then OrderDate else null end),
second_order_date=MAX(case when rowid=2 then OrderDate else null end),
third_order_date=MAX(case when rowid=3 then OrderDate else null end),
fourth_order_date=MAX(case when rowid=4 then OrderDate else null end)
from
(
select *,
rowid = row_number() over (partition by UserId order by OrderDate)
from #ORDERS
) X
group by UserId;
-
THANKS!!
both solutions are great!
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply