top 10 from each group - SQL Server Forums

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

All Forums

Old Forums

CLOSED - General SQL Server

top 10 from each group

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-27 : 09:28:01

Jeremy writes "HI, I have read many posts on the newsgroups, forums, FAQs,etc and for the life of me I can't figure this one out so I apologize if this has been answered before somwhere.

Currently I'm using asp to cycle thru a list of staff members on my www site (in a table called STAFF). Then while looping I call a stored procedure to get the top 10 albums last listened to (order by date desc).

here is my sp:
CREATE PROCEDURE spPlayLists
(@name as varchar(15))
AS
SELECT TOP 10 Artist, Album, [Date]
FROM dbo.Playlists
WHERE (Staff = @name)
ORDER BY [Date] DESC
GO

is there any way to make a stored procedure to loop through my staff members list and call this stored procedure above? or maybe make into one sp?

I have tried: (using a cursor and loop- which returns only one staff name)

CREATE PROCEDURE spAllPlayListsNew AS
declare curse cursor for
SELECT Name
FROM dbo.Staff
open curse
declare @ppl varchar(30)
fetch next from curse into @ppl
while (@@fetch_status=0)
begin
exec ('spPlayLists ' + @ppl)
fetch next from curse into @ppl
end
close curse
deallocate curse
GO

and even some other funky code (subquery? ):

CREATE PROCEDURE sptest AS
begin
declare @str varchar(255)
declare @ppl varchar(50)
select @str='exec (''spPlayLists ' + @ppl + ')'
exec (@str)
end
GO

thanks for the help in advance."

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2002-04-27 : 10:19:50

Try this one:

SELECT Staff.Name, Playlists.Artist, Playlists.Album, Playlists.Date
FROM Staff INNER JOIN Playlists ON Staff.Name = Playlists.Staff
  AND Playlist.ID IN (SELECT TOP 10 ID FROM Playlists 
                       WHERE Staff=Staff.Name ORDER BY Date DESC)

I'm assuming your Playlists table has a primary key.... I haven't tested the query, but this is basic structure should work fine.

btw - the reason you're SP isn't working is that it exits the procedure as soon as the first SELECT statement has run.

Tim

Nazim
A custom title

1408 Posts

Posted - 2002-04-28 : 02:28:27

check this too
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=15054

--------------------------------------------------------------

jrb2971
Starting Member

5 Posts

Posted - 2002-04-29 : 13:28:55

thanks that did the try with some modifications:
(also would it be more efficient to use a select distinct playlists.staff instead of the other staff table?)

SELECT TOP 100 PERCENT dbo.Staff.Name, dbo.Playlists.Artist, dbo.Playlists.Album, dbo.Playlists.[Date]
FROM dbo.Staff INNER JOIN
dbo.Playlists ON dbo.Staff.Name = dbo.Playlists.Staff AND dbo.Playlists.id IN
(SELECT TOP 10 ID
FROM Playlists
WHERE Staff = Staff.Name
ORDER BY Date DESC)
ORDER BY dbo.Staff.Name, dbo.Playlists.[Date] DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources