Last 3 Dates
-
Hehe I see the confusion here. I was referring to the fact that the FINAL select will, on very rare occasion, return top 2 + tie like this >>
set nocount on
use northwind
select
t1.CustomerID
, t1.OrderDate
, dtOverFlow.Total
from
orders t1
inner join
(select
t1.CustomerID
, count(*) as Total
from
orders t1
where
t1.OrderDate in
(
select top 2 with ties -- or not
t2.OrderDate
from
orders t2
where
t2.CustomerID = t1.CustomerID
order by
t2.OrderDate desc
)
group by t1.CustomerID
having count(*) > 2
) dtOverFlow
on t1.CustomerID = dtOverFlow.CustomerID
where
t1.OrderDate in
(
select top 2 with ties
t2.OrderDate
from
orders t2
where
t2.CustomerID = t1.CustomerID
order by
t2.OrderDate desc
)
order by
t1.CustomerID
, t1.OrderDate desc
set nocount off
returns :
CustomerID OrderDate Total
---------- ------------------------------------------------------ -----------
SAVEA 1998-05-01 00:00:00.000 3
SAVEA 1998-04-17 00:00:00.000 3
SAVEA 1998-04-17 00:00:00.000 3
Viewing post 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply