SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Top n records for each month/year, grouped by month year
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 07/18/2001 :  08:11:13  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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  Show Profile  Reply with Quote
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

Go to Top of Page

jns
Starting Member

USA
19 Posts

Posted - 07/18/2001 :  09:41:52  Show Profile  Reply with Quote
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


Go to Top of Page

JustinBigelow
SQL Gigolo

USA
1157 Posts

Posted - 07/18/2001 :  09:55:49  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000