[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 363
  • 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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

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