asked on

Ordery SQL query while using TOP keyword

Hi,

Please help me with thie query. I would like to query tables

CUSTOMER C
TICKET_HISTORY T

returning the top 3 T.TICKET DATES

ordered by C.CUST_NO

both tables have key CUST_NO

So having record set like this.

C.CUST_NO T.TKT_DAT T.TKT_NO

10001 2010-01-01 13456
10001 2009-01-01 13444
10001 2008-01-01 34344
10002 2010-01-01 34343
10002 2009-01-01 34341
10002 2008-01-01 34342

dwkor

;with CTE(Cust_No, Tkt_Dat, Tkt_No, RowNum)
as
(
select c.cust_no, t.tkt_dat, t.tkt_no, row_number() over (order by t.tkt_Dat partition by c.cust_no) as RowNum
from customer c join ticket_history t on
c.cust_no = t.cust_no
)
select Cust_No, Tkt_Dat, Tkt_No
from CTE
where RowNum <= 3
order y Cust_No, Tkt_Dat

cyberkiwi

select cust_no, tkt_dat, tkt_no
from
(
select rn=row_number() over (partition by c.cust_no order by t.tkt_dat desc)
,C.CUST_NO ,T.TKT_DAT ,T.TKT_NO
from CUSTOMER C
left join TICKET_HISTORY T on C.cust_no=t.cust_no
) sq
where rn <= 3
order by CUST_NO

You can change left join to inner join depending on whether you want to see customers without tickets.

ASKER CERTIFIED SOLUTION

cyberkiwi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial