Cool problem. Here's my solution, this is on MS-SQL 2008, just run the
whole thing.
-- create the test table
CREATE TABLE X1 (
Id INT IDENTITY(1,1) PRIMARY KEY,
Data VARCHAR(10)
)
GO
INSERT INTO X1 (DATA) SELECT TOP 3 'spam' FROM SYS.OBJECTS
INSERT INTO X1 (DATA) SELECT TOP 10 'lamb' FROM SYS.OBJECTS
INSERT INTO X1 (DATA) SELECT TOP 1 'rank' FROM SYS.OBJECTS
SELECT * FROM X1 -- display the test data
-- here's the start of the script, you can put in a stored proc or function
SET NOCOUNT ON
DECLARE @DATA TABLE(DATA VARCHAR(10))
DECLARE @D VARCHAR(10),
@C INT,
@SQL VARCHAR(255)
-- put keywords in a table
INSERT INTO @DATA SELECT 'spam' UNION SELECT 'ham' UNION SELECT 'lamb'
UNION SELECT 'rank'
SELECT Data,
CASE
WHEN COUNT(1) < 2 THEN COUNT(1)
ELSE 2
END Count
INTO #LimitedData
FROM X1
WHERE Data IN (SELECT Data FROM @DATA)
GROUP BY Data
/*
by now, #LimitedData could be enough as it already gives you a count of
the data that matches the keywords, and you can use CF instead of a cursor
to build out the SELECT (use a SELECT UNION), like so (not tested):
<cfquery name="q">select data, count from #limitedData</cfquery>
<cfoutput query="q">
sql &= "#q.currenrow : "UNION" ? ""# SELECT TOP #q.count# *
FROM X1 WHERE Data = '#data#'"
</cfoutput>
but, here's the cursor code anyway
(i don't like cursors, so i hope there's a way to do this without
a cursor, all in SQL)
*/
CREATE TABLE #Results (Id int, Data varchar(10))
DECLARE C CURSOR FOR
SELECT Data, Count FROM #LimitedData
DELETE FROM @DATA
OPEN C
FETCH NEXT FROM C INTO @d, @c
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE sp_executesql
N'INSERT INTO #Results (id, data) SELECT TOP (@c) Id, Data
FROM X1 WHERE data = @d',
N'@c INT, @d VARCHAR(10)',
@c = @c, @d = @d;
FETCH NEXT FROM C INTO @d, @c
END
CLOSE C
DEALLOCATE C
SELECT * FROM #Results -- display results
DROP TABLE #LimitedData
DROP TABLE #Results
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3388
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/sql/unsubscribe.cfm
