Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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  
 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.03 seconds. Powered By: Snitz Forums 2000