How to nest inline views 1

[cmmrfrds]

I need the last 3 dates for an order and these need to be returned in 1 row. I used an inline view to get the last 2 orders but do not know how to add a 2nd level of inline view to get the 3rd last order. Also, need to control for the fact that there may be only 1 or 2 order dates, or that there can be 2 orders on the same date. Here is what I did to get the last 2 dates.

select
cm.customerid,
max(cm.orderdate) as ordate2,
max(c1.ordate) as ordate1
from orders as cm
inner join
(select
customerid,
max(orderdate) as ordate,
count(*) as cnt1
from orders
group by customerid) as c1
on c1.customerid = cm.customerid
where cm.orderdate < c1.ordate or cnt1 = 1
group by cm.customerid

Can anyone help me on this?
Thank you.

 

[nagornyi]

select
(
select orderdate from
(select orderdate, rownum num from orders order by orderdate desc)
where num=1
) 
||' '||
(
select orderdate from
(select orderdate, rownum num from orders order by orderdate desc)
where num=2
) 
||' '||
(
select orderdate from
(select orderdate, rownum num from orders order by orderdate desc)
where num=3
) 
from dual

 

[cmmrfrds]

Thank you nargonyi for the reply. Since I need a column for each date and the customer id would the following work.

select
customerid,
(
select orderdate from
(select orderdate, rownum num from orders
where A.customerid = customerid order by orderdate desc)
where num=1
) as top1date,
(
select orderdate from
(select orderdate, rownum num from orders
where A.customerid = customerid order by orderdate desc)
where num=2
) as top2date,
(
select orderdate from
(select orderdate, rownum num from orders
where A.customerid = customerid order by orderdate desc)
where num=3
) as top3date
from orders A
Group by A.customerid

 

[nagornyi]

I am afraid that query won't run because A.customerid will not be seen from the nested query. Here is how i was able to do it:

select cid customerid, od1 top1date, od2 top2date, od3 top3date from
(
select d1.cid, d1.od od1, d2.od od2, d3.od od3 , d1.num from
(select cid, od, rownum num from(select customerid cid, orderdate od from orders order by customerid, orderdate desc)) d1
,(select cid, od, rownum num from(select customerid cid, orderdate od from orders order by customerid, orderdate desc)) d2
,(select cid, od, rownum num from(select customerid cid, orderdate od from orders order by customerid, orderdate desc)) d3
where d1.cid = d2.cid(+)
and d2.cid = d3.cid(+)
and d1.num=d2.num(+)-1
and d2.num=d3.num(+)-1
)
where (cid, num) in
(select cid, min(num) from
(
select d1.cid, d1.od od1, d2.od od2, d3.od od3 , d1.num from
(
select cid, od, rownum num from(select customerid cid, orderdate od from orders order by customerid, orderdate desc)) d1
,(select cid, od, rownum num from(select customerid cid, orderdate od from orders order by customerid, orderdate desc)) d2
,(select cid, od, rownum num from(select customerid cid, orderdate od from orders order by customerid, orderdate desc)) d3
where d1.cid = d2.cid(+)
and d2.cid = d3.cid(+)
and d1.num=d2.num(+)-1
and d2.num=d3.num(+)-1
)
group by cid
)

 

[cmmrfrds]

Thank you for your persistence in coming up with a solution.