September 5, 2003 at 3:05 pm
I have a table X with two columns date and hits. This table may have few milllion records.
Sample data in the table is like (for simplicity sake I represented dates as integers)
date hits
----------- -----------
1 100
2 125
3 145
3 155
4 165
5 100
6 125
7 200
8 250
9 200
10 250
11 100
11 165
I would like to get the dates with top 5 hits and their corresponding hits.
The trick is if I have multiple dates for the same hits value use the latest date.
In the sample above, if 100 were one of the top 5 hits, I would like to see 5 as the date.
Here is what I have
select ct1.hits, max(ct1.date) as date from datehits CT1 inner join
(select top 5 hits from datehits group by hits order by hits desc ) as CT2
on CT1.hits = CT2.hits
group by ct1.hits
order by ct1.hits desc
And I do get what I want:
hits date
----------- -----------
250 10
200 9
165 11
155 3
145 3
My questions are:
Is there any better way than this?
Why can't I say, the following as the table CT2, since what I want is the top 5 distinct hits. (I get syntax error)
(select top 5 distinct( hits ) from datehits ) as CT2
September 7, 2003 at 3:25 am
Hope it helps a bit.
Set NoCount on
Go
Create Table Hits(LeDate int,aHits Int)
Go
Insert Hits values (1,100)
Insert Hits values(2,125)
Insert Hits values(3,145)
Insert Hits values(3,155)
Insert Hits values(4,165)
Insert Hits values(5,100)
Insert Hits values(6,125)
Insert Hits values(7,200)
Insert Hits values(8,250)
Insert Hits values(9,200)
Insert Hits values(10,250)
Insert Hits values(11,100)
Insert Hits values(11,165)
Go
Set NoCount off
Go
Select Top 5 ct1.ahits, Max(ct1.ledate)
From hits CT1
Group by ct1.ahits
Order by ct1.ahits desc
Go
Drop table Hits
Go
September 8, 2003 at 5:15 am
The above answer is what you need, I guess...
I just wanted to comment on the error you were receiving: Reason is that the order is incorrect, distinct should come first, before top. If you change it to SELECT DISTINCT TOP 5, it will work - though I didn't test whether it would help in this particular case, because the solution proposed by 5409045121009 is much easier.
Edited by - Vladan on 09/08/2003 08:19:38 AM
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply