| Author |
Topic |
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
 Posted - 2004-12-30 : 11:29:00
|
| Hi,Lets say I have a catalog of records and I want an administrator to be able to create new 'Top' lists. e.g. Top 5 80s records, Top 20 Blues Records.How do I store these filterings in the database?Should I just have a 'TopLists' table which has a column for the list's name and then another column to hold the SQL for the query or else the name of a stored procedure containing the query?Cheers,XF. |
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2004-12-30 : 11:43:49
|
| How are top lists being derived. Based on page hits, someones opinion, etc..? |
 |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-12-30 : 11:47:56
|
use procedures if there aren't much searches. if you feel like doing all those sproc's.another option is to save the query itself and execute it using sp_executesql. it caches the exec plans for each queray you run.Go with the flow & have fun! Else fight the flow  |
|
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2004-12-30 : 12:00:09
|
| The lists would be derived from the number of plays of a record - its a juke box that I saw in my local pub. |
|
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2004-12-30 : 13:03:28
|
quote:
Originally posted by X-Factor
The lists would be derived from the number of plays of a record - its a juke box that I saw in my local pub.
OK. How is "number of plays of a record" being stored. Or is it? Or is this your question? |
|
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2004-12-30 : 14:03:55
|
| Well I think there'd be an integer column on the record track which is incremented every time the track is played.So a query might be like this...SELECT TOP 5 * FROM Tracks t INNER JOIN Categories c ON t.catID = c.catID WHERE c.name = "Blues" ORDER BY t.playCount DESCBut I don't think this is important. What's important is how this query is stored in the database. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-12-30 : 14:12:17
|
| well then you have 2 options i mentioned.Go with the flow & have fun! Else fight the flow |
|
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2004-12-30 : 14:44:29
|
The reason I keep asking what appears to be obscure questions is that I am trying to understand why you would consider "storing" a filtering query... Does'nt make much sense to me. Just create a proc or param query for search or shell out the filter in d-sql and pass in the top n value and the music type. Something like: create proc dbo.upTopCategories@n INT = null,@CategoryName NVARCHAR(40) = nullASset nocount ondeclare @sql varchar(1000)select @sql = 'SELECT TOP ' + CAST(@n as varchar(3)) + ' * 'select @sql = @sql + 'FROM Tracks t 'select @sql = @sql + 'INNER JOIN Categories c ON t.catID = c.catID 'select @sql = @sql + 'WHERE c.name = @CategoryName 'select @sql = @sql + 'ORDER BY t.playCount DESC'--PRINT @sqlEXEC sp_executesql @sql, N'@CategoryName nvarchar(40)', @CategoryName = @CategoryNamego |
|
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2004-12-30 : 15:34:14
|
| Thanks for your replies.I'd want to store them because I'd want to give an administrator the power to create whatever top list they wanted and not be constrained to existing sps. |
|
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2004-12-30 : 15:50:41
|
| ...but I suppose that all queries will be limited by the query designer form so the sp would only need to be as complicated as the form so I think you're right - I would just need one of the dynamic SQL type queries which are used to satisfy detailed search requests.But I'd still need to keep a record of the parameters used for a particular list. |
|
|
|
|
Storing search queries