Top 20 Daily...

[katehol]

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

 

[r937]

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

 

[katehol]

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

 

[r937]

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

 

[katehol]

Yes, I did try your solution and it gave me 198 rows for 2008-05-21, rather than 20 )

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

 

[r937]

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

 

[katehol]

Nah, that still didn't work. Think I'll have to try to come up with a different solution.
Thanks anyway

 

[PWise]

not tested

Select distinct datefield
from Table
inner join(SELECT TOP 20 * FROM Table
           ORDER by [Count] DESC) tops
on tops.datefield = Table.datefield

 

[PWise]

sorry no good

 

[katehol]

Nope, 'fraid not.
Thanks anyway

 

[r937]

Nah, that still didn't work.

it did so!


please don't blame the query if your data is bad

>:-<

r937.com | rudy.ca

 

[katehol]

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.

 

[r937]

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