Solved

Ordery SQL query while using TOP keyword

Posted on 2010-08-13
3
357 Views
Last Modified: 2012-06-27
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
Comment
Question by:AD1080
  • 2
3 Comments
 
LVL 13

Expert Comment

by:dwkor
ID: 33434911
;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
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33434914
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
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 33434916
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question