• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 364
  • Last Modified:

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
0
AD1080
Asked:
AD1080
  • 2
1 Solution
 
dwkorCommented:
;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
0
 
cyberkiwiCommented:
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.
0
 
cyberkiwiCommented:
If you want the dates ordered backwards like you show in sample

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, tkt_dat desc
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now