Solved

Ordery SQL query while using TOP keyword

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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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…

746 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

11 Experts available now in Live!

Get 1:1 Help Now