Solved

Ordery SQL query while using TOP keyword

Posted on 2010-08-13
3
355 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Maintenance Plan 3 29
CROSS APPLY 4 44
Upgrading an old legacy SQL server 20082 to 2014 - TSQL compatibility 3 37
Max Consumption Rate (MCR) 3 33
Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
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…

910 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now