Another dynamic top question - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Another dynamic top question

Author

Topic

Limbic
Starting Member

15 Posts

Posted - 2007-12-13 : 05:20:48

Hi there,

After having spent some time with the TOP(@Rows) keyword, I still have no clue how to do this when the number of rows to be retrieved actually is a value of a joined table.




CREATE TABLE Categories
(
	CatID INT,
	MaxSkills INT -- That is the field for TOP?
)

CREATE TABLE CategorySkills
(
	CatID INT,
	SkillID INT
)

CREATE TABLE Skills
(
	SkillID INT,
	Title NVARCHAR(50)
)

INSERT INTO Categories VALUES(1, 2)
INSERT INTO Categories VALUES(2, 3)

INSERT INTO CategorySkills VALUES(1, 1)
INSERT INTO CategorySkills VALUES(1, 2)
INSERT INTO CategorySkills VALUES(1, 3)
INSERT INTO CategorySkills VALUES(1, 4)


INSERT INTO CategorySkills VALUES(2, 1)
INSERT INTO CategorySkills VALUES(2, 6)
INSERT INTO CategorySkills VALUES(2, 7)
INSERT INTO CategorySkills VALUES(2, 8)
INSERT INTO CategorySkills VALUES(2, 9)



INSERT INTO Skills VALUES(1, 'First')
INSERT INTO Skills VALUES(2, 'Second')
INSERT INTO Skills VALUES(3, 'Third')
INSERT INTO Skills VALUES(4, 'Fourth')
INSERT INTO Skills VALUES(5, 'Fifth')
INSERT INTO Skills VALUES(6, 'Sixth')
INSERT INTO Skills VALUES(7, 'Seventh')
INSERT INTO Skills VALUES(8, 'Eight')
INSERT INTO Skills VALUES(9, 'Ninth')


SELECT c.CatID, s.SkillID, s.Title
FROM Categories c
	INNER JOIN CategorySkills cs
		ON c.CatID = cs.CatID
	INNER JOIN Skills s
		ON cs.SkillID = s.SkillID
ORDER BY c.CatID

In the example above, I only want to retrieve 2 skills from catID 1 and 3 skills catID 2 (depending on categories.MaxSkills). Furthermore I have to randomize the results for a catID.
I had a look a round and found this which works fine, but I don't know how to use the TOP with a value of a joined field.
SELECT TOP 10 * FROM skills ORDER BY NEWID()

I hope someone can help me with this!

Many thanks,
Limbic

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-13 : 12:50:56

try this:-

CREATE TABLE #temp
(
ID int IDENTITY(1,1),
CatID int,
SkillID int,
Title nvarchar(50),
RecCount int,
RowNo int
)

INSERT INTO #temp(CatID, SkillID,Title,RecCount)
SELECT c.CatID, s.SkillID, s.Title,temp.Rec_Count
FROM Categories c
INNER JOIN CategorySkills cs
ON c.CatID = cs.CatID
INNER JOIN Skills s
ON cs.SkillID = s.SkillID
INNER JOIN (SELECT CatID,COUNT(*) AS 'Rec_Count'
FROM Skills
GROUP BY CatID) temp
ON temp.CatID=c.CatID
ORDER BY c.CatID,NEWID()

UPDATE #t
SET #t.RowNo=#t.ID-decr.Dec
FROM #temp #t
INNER JOIN
(
SELECT CatID,SUM(RecCount) AS 'Dec'
FROM
(SELECT #t1.CatID,t2.RecCount
FROM #temp #t1
INNER JOIN(SELECT CatID,MIN(RecCount) AS 'RecCount' FROM #temp GROUP BY CatID) t2
ON t2.CatID < #t.CatID) m
GROUP BY CatID)decr
ON decr.CatID=#t.CatID

SELECT * FROM #temp t
INNER JOIN Skills s
ON t.CatID=s.CatID
WHERE t.RowNo <=s.MaxSkills

Limbic
Starting Member

15 Posts

Posted - 2007-12-13 : 17:59:52

Hi there,

thanks for the reply. I get some sql errors when I try to execute it.
Is there one temporary table missing?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-14 : 12:34:31

Last update is as follows. Are you sure you didnt miss anything.

UPDATE #t
SET #t.RowNo=#t.ID-decr.Dec
FROM #temp #t
INNER JOIN
(
SELECT m.CatID,SUM(m.RecCount) AS 'Dec'
FROM (SELECT #t1.CatID,t2.RecCount
FROM #temp #t1
INNER JOIN
(SELECT CatID,MIN(RecCount) AS 'RecCount' FROM #temp GROUP BY CatID) t2
ON t2.CatID < #t.CatID) m GROUP BY m.CatID)decr
ON decr.CatID=#t.CatID

Limbic
Starting Member

15 Posts

Posted - 2007-12-14 : 13:18:42

Hi,

it's probably me but when I try to execute your update statement
than I get the follwing error message

The column prefix '#t' does not match with a table name or alias name used in the query.

Before that, I used the create #temp statement and executed your insert statement as well. (There were are minor error as the Skills table hasn't a catID).

Any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-17 : 04:19:46

Try this:-
UPDATE #t
SET #t.RowNo=#t.ID-decr.Dec
FROM #temp #t
INNER JOIN
(
SELECT m.CatID,SUM(m.RecCount) AS 'Dec'
FROM (SELECT #t1.CatID,t2.RecCount
FROM #temp #t1
INNER JOIN
(SELECT CatID,MIN(RecCount) AS 'RecCount' FROM #temp GROUP BY CatID) t2
ON t2.CatID < #t1.CatID) m GROUP BY m.CatID)decr
ON decr.CatID=#t.CatID

Limbic
Starting Member

15 Posts

Posted - 2007-12-19 : 12:01:58

Hi,

thanks that works now but it only gives me back the data for one category.


UPDATE #t
SET #t.RowNo=#t.ID-decr.Dec
FROM #temp #t
INNER JOIN
(
	SELECT m.CatID,SUM(m.RecCount) AS 'Dec'
	FROM 
	(
                --This has only one category left
		SELECT #t1.CatID,t2.RecCount
		FROM #temp #t1
		INNER JOIN
		(
                        -- this returns 2 categories which is correct
			SELECT CatID, MIN(RecCount) AS 'RecCount' 
			FROM #temp 
			GROUP BY CatID
		) t2
		ON t2.CatID < #t1.CatID
	) m 
	GROUP BY m.CatID
) decr
ON decr.CatID=#t.CatID

Maybe this has something to do with the insert statement.


INSERT INTO #temp(CatID, SkillID,Title,RecCount)
SELECT c.CatID, s.SkillID, s.Title,temp.Rec_Count
FROM Categories c
INNER JOIN CategorySkills cs
ON c.CatID = cs.CatID
INNER JOIN Skills s
ON cs.SkillID = s.SkillID
INNER JOIN (
-- should this be categories or categoryskills?

SELECT CatID,COUNT(*) AS 'Rec_Count'
FROM Skills
GROUP BY CatID

) temp
ON temp.CatID=c.CatID
ORDER BY c.CatID,NEWID()

Your help is very much appreciated!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 12:20:12

[code]-- Prepare sample data
DECLARE @Categories TABLE (CatID INT, MaxSkills INT)

INSERT @Categories
SELECT 1, 2 UNION ALL
SELECT 2, 3

DECLARE @CategorySkills TABLE (CatID INT, SkillID INT)

INSERT @CategorySkills
SELECT 1, 1 UNION ALL
SELECT 1, 2 UNION ALL
SELECT 1, 3 UNION ALL
SELECT 1, 4 UNION ALL
SELECT 2, 1 UNION ALL
SELECT 2, 6 UNION ALL
SELECT 2, 7 UNION ALL
SELECT 2, 8 UNION ALL
SELECT 2, 9

DECLARE @Skills TABLE (SkillID INT, Title VARCHAR(7))

INSERT @Skills
SELECT 1, 'First' UNION ALL
SELECT 2, 'Second' UNION ALL
SELECT 3, 'Third' UNION ALL
SELECT 4, 'Fourth' UNION ALL
SELECT 5, 'Fifth' UNION ALL
SELECT 6, 'Sixth' UNION ALL
SELECT 7, 'Seventh' UNION ALL
SELECT 8, 'Eight' UNION ALL
SELECT 9, 'Ninth'

-- Initialize staging area
DECLARE @Stage TABLE (RowID INT IDENTITY, CatID INT, SkillID INT, Title VARCHAR(9), MaxSkills INT)

INSERT @Stage
(
CatID,
SkillID,
Title,
MaxSkills
)
SELECT cs.CatID,
cs.SkillID,
s.Title,
c.MaxSkills
FROM @CategorySkills AS cs
INNER JOIN @Skills AS s ON s.SkillID = cs.SkillID
INNER JOIN @Categories AS c ON c.CatID = cs.CatID
ORDER BY cs.CatID,
NEWID()

-- Show the expected output
SELECT s.CatID,
s.SkillID,
s.Title
FROM (
SELECT CatID,
MIN(RowID) AS MinRow,
MIN(RowID + MaxSkills - 1) AS MaxRow
FROM @Stage
GROUP BY CatID
) AS p
INNER JOIN @Stage AS s ON s.CatID = p.CatID
WHERE s.RowID BETWEEN p.MinRow AND p.MaxRow
ORDER BY s.RowID[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 13:01:13

For more stable solution, replace

DECLARE @Stage TABLE (RowID INT IDENTITY, CatID INT, SkillID INT, Title VARCHAR(9), MaxSkills INT)

with

DECLARE @Stage TABLE (RowID INT IDENTITY(1, 1), CatID INT, SkillID INT, Title VARCHAR(9), MaxSkills INT)

E 12°55'05.25"
N 56°04'39.16"

Limbic
Starting Member

15 Posts

Posted - 2007-12-19 : 16:38:57

Peso! Great, that works brilliantly. Thanks very much!

visakh16, thank you as well.

You've been of great help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources