| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
 Posted - 07/18/2001 : 08:11:13
|
Jeremy writes "Based upon the following table
DATE PID
07/15/2000 3
07/15/2000 4
07/15/2000 8
07/15/2000 3
07/15/2000 3
07/15/2000 6
07/15/2000 6
07/15/2000 4
08/15/2000 3
08/15/2000 3
08/15/2000 4
08/15/2000 5
08/15/2000 9
08/15/2000 9
08/15/2000 4
I need to be able to return the top 2 PIDS in each month/year. By using the TOP function I just get two records when I need to get the top two in July 2000, August 2000, etc. Any suggestions?" |
|
|
JustinBigelow
SQL Gigolo
USA
1157 Posts |
Posted - 07/18/2001 : 09:35:29
|
I think this will do what you need...
select pid, pdate
from pids p1
where pid in (select top 2 pid from pids p2 where p2.pdate = p1.pdate order by pid desc)
order by pid, pdate desc
Justin
|
 |
|
|
jns
Starting Member
USA
19 Posts |
Posted - 07/18/2001 : 09:41:52
|
That would get the Top two per day but I think the concept would work if we search and compared based upon DATEPART(mm,date) and DATEPART(yy,date) and compaged between the 'two' tables. Alternatively, for posterity, here is the stored procedure I came up with. Probably more intensive then it needs to be and should be optimized.
CREATE procedure dbo.sp_toppagespermonth @site INT AS
BEGIN TRANSACTION
DECLARE @month varchar(50)
DECLARE @year INT
DECLARE @mm INT
create table #t_activity (
m INT NULL,
month varchar(255),
year INT,
visits INT,
page varchar(255) )
declare top_schedule cursor for
SELECT DISTINCT DATEPART(mm, created), DATENAME(mm, created), DATEPART(yy,created) FROM activity WHERE site=@site ORDER by DATEPART(mm, created) DESC, DATEPART(yy,created) DESC for read only
open top_schedule
fetch top_schedule into @mm, @month, @year
while @@fetch_status = 0
begin
INSERT INTO #t_activity
SELECT TOP 5 DATEPART(mm,created) as m, DATENAME(month,created) as month, DATEPART(yy, created) as year,
count(id) as visits, SUBSTRING(page,(SELECT LEN(path) + 1 FROM sites WHERE id=@site),100) as page
FROM activity WHERE site=@site AND DATEPART(mm,created)=@mm AND DATEPART(yy,created)=@year
GROUP by page,DATEPART(mm,created), DATENAME(month,created), DATEPART(yy, created)
ORDER by DATEPART(yy, created) DESC, DATEPART(mm,created) DESC, DATENAME(month,created), count(id) DESC
fetch top_schedule into @mm, @month, @year
end
close top_schedule
deallocate top_schedule
SELECT * FROM #t_activity
COMMIT TRANSACTION
GO
|
|
|
|
JustinBigelow
SQL Gigolo
USA
1157 Posts |
Posted - 07/18/2001 : 09:55:49
|
You're right, my final order by was also not keeping the months together since I first order by pid rather than date. Here is version 2 
select pid, pdate
from pids p1
where pid in (select top 2 pid from pids p2 where
datepart(mm, p2.pdate) = datepart(mm, p1.pdate) and
datepart(yy, p2.pdate) = datepart(yy, p1.pdate) order by pid desc)
order by pdate
Justin
Edited by - justinbigelow on 07/18/2001 09:56:11
Edited by - justinbigelow on 07/18/2001 09:58:12 |
|
|
| |
Topic |
|
|
|
Top n records for each month/year, grouped by month year
