Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Top 20 Daily...

Status
Not open for further replies.

katehol

Technical User
May 2, 2007
57
GB
An easy little query for someone - I appear to be having brain freeze over it (think it's because it's a Friday...)

I want to be able to show the daily top 20 pages from a table. If I run the following query, I only get the top 20 from the first date stored in the table - how would I show the top 20 pages from each date stored in the table?

SELECT TOP 20 * FROM Table
ORDER BY Date, Count DESC

Thanks
 
Code:
SELECT [date]
     , [count]
  FROM daTable AS t
 WHERE (
       SELECT COUNT(*)
         FROM daTable
        WHERE [date] = t.[date]
          AND [count] > T.[count] ) < 20
there is also a nice way of doing this in SQL 2005 using ROW_NUMBER OVER

r937.com | rudy.ca
 
Sorry - I obviously didn't explain myself clearly in my original question. I have a table like this

Page Count Date
Page1 | 414 | 2008-05-21
Page6 | 414 | 2008-05-21
Page4 | 414 | 2008-05-21
Page2 | 414 | 2008-05-21
...
Page1 | 414 | 2008-05-22
Page5 | 414 | 2008-05-22
Page3 | 414 | 2008-05-22
Page7 | 414 | 2008-05-22
Page4 | 414 | 2008-05-22
...
Page2 | 414 | 2008-05-23
Page5 | 414 | 2008-05-23
Page3 | 414 | 2008-05-23

If I write a query like the one below against the table it just brings back the top 20 from the 2008-05-21. I want it to bring back the top 20 from the 21st, the top 20 from the 22nd and the top 20 from the 23rd.

SELECT TOP 20 * FROM Table
ORDER BY Date, Count DESC
 
could you please give me a better sample of test data to test with?

there are too many ties in that sample

also, did you actually try my solution on your real data?

r937.com | rudy.ca
 
Yes, I did try your solution and it gave me 198 rows for 2008-05-21, rather than 20 :eek:)

Page Count Date
Page1 | 414 | 2008-05-21
Page2 | 123 | 2008-05-21
Page3 | 373 | 2008-05-21
Page4 | 284 | 2008-05-21
Page5 | 414 | 2008-05-21
Page6 | 123 | 2008-05-21
Page7 | 373 | 2008-05-21
Page8 | 284 | 2008-05-21
Page9 | 414 | 2008-05-21
Page10| 123 | 2008-05-21
Page11| 373 | 2008-05-21
Page12| 284 | 2008-05-21
...
Page1 | 277 | 2008-05-22
Page2 | 111 | 2008-05-22
Page3 | 345 | 2008-05-22
Page4 | 864 | 2008-05-22
Page5 | 333 | 2008-05-22
Page6 | 277 | 2008-05-22
Page7 | 111 | 2008-05-22
Page8 | 345 | 2008-05-22
Page9 | 864 | 2008-05-22
Page10| 333 | 2008-05-22
Page11| 277 | 2008-05-22
Page12| 111 | 2008-05-22
Page13| 345 | 2008-05-22
Page14| 864 | 2008-05-22
Page15| 333 | 2008-05-22
...
Page1 | 414 | 2008-05-23
Page2 | 367 | 2008-05-23
Page3 | 459 | 2008-05-23
Page4 | 414 | 2008-05-23
Page5 | 367 | 2008-05-23
Page6 | 459 | 2008-05-23
Page7 | 414 | 2008-05-23
Page8 | 367 | 2008-05-23
Page9 | 459 | 2008-05-23

If you could show me how to get the top 5 counts from each day, that would be great.

Thanks

 
i added Page to the SELECT list, and added an ORDER BY clause, and here's what my query produces for the top 5 in each date:

2008-05-21 414 Page1
2008-05-21 414 Page5
2008-05-21 414 Page9
2008-05-21 373 Page11
2008-05-21 373 Page7
2008-05-21 373 Page3

2008-05-22 864 Page4
2008-05-22 864 Page9
2008-05-22 864 Page14
2008-05-22 345 Page13
2008-05-22 345 Page8
2008-05-22 345 Page3

2008-05-23 459 Page3
2008-05-23 459 Page6
2008-05-23 459 Page9
2008-05-23 414 Page7
2008-05-23 414 Page4
2008-05-23 414 Page1

notice that there are more than 5 in each group because of ties in the 5th spot

maybe you have 198 ties for 2008-05-21? ;-)

r937.com | rudy.ca
 
Nah, that still didn't work. Think I'll have to try to come up with a different solution.
Thanks anyway
 
not tested
Code:
Select distinct datefield
from Table
inner join(SELECT TOP 20 * FROM Table
           ORDER by [Count] DESC) tops
on tops.datefield = Table.datefield
 
My data is not actually bad. I have only put a made up set of data for you, as I didn't want to post all half a million rows for you. I know what I am expecting from the results, and I'm afraid the query you gave me did not give me the results I should have got.
Therefore, I said thanks anyway but this query is not working. Don't see anything wrong in that.
 
my query worked fine on every set of data you provided

in addition to which, i have been writing SQL for over 20 years and i know that the query produces the right results

i don't see anything wrong with me insisting that the query is working

perhaps you could show the exact query that you ran? maybe there's a typo, or something...

:)

r937.com | rudy.ca
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top