SP and group by

[vlitim]

I have a number of tables with a contributor and hits column, what I need to do is find the top 5 contributors that have had the most hits.

I am using the below code but it doesn't work, does nyone know what terrible mistake I am making?


--Create a temporary table
CREATE TABLE #TempItems
(
ID int IDENTITY,
Hits int,
Contributor int
)

-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (contributor,hits)
SELECT contributor,hits
FROM careers
WHERE active=1
UNION
SELECT contributor,hits
FROM going_out
WHERE active=1
UNION
SELECT contributor,hits
FROM mag
WHERE active=1
UNION
SELECT contributor,hits
FROM mind_body_soul
WHERE active=1
UNION
SELECT contributor,hits
FROM sound_vision
WHERE active=1
UNION
SELECT contributor,hits
FROM sport_rec
WHERE active=1
UNION
SELECT contributor,hits
FROM travel
WHERE active=1

-- Find out the first and last record we want

-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT contributor,sum(hits)
FROM #TempItems
WHERE ID < 6 group by contributor



cheers

 

[rajeevnandanmishra]

Hi there,
If you are using sql 7.0 or above
----------------------------------[/b]
SELECT TOP 5 * FROM (
SELECT contributor,hits
FROM careers
WHERE active=1
UNION
SELECT contributor,hits
FROM going_out
WHERE active=1
UNION
SELECT contributor,hits
FROM mag
WHERE active=1
UNION
SELECT contributor,hits
FROM mind_body_soul
WHERE active=1
UNION
SELECT contributor,hits
FROM sound_vision
WHERE active=1
UNION
SELECT contributor,hits
FROM sport_rec
WHERE active=1
UNION
SELECT contributor,hits
FROM travel
WHERE active=1
) X
ORDER BY 1 DESC
[/b]----------------------------------

If you are using sql 6.5 or below
----------------------------------
CREATE TABLE #TempItems
(
Contributor int,
Hits int
)

INSERT #TempItems
SELECT contributor,hits
FROM careers
WHERE active=1
UNION
SELECT contributor,hits
FROM going_out
WHERE active=1
UNION
SELECT contributor,hits
FROM mag
WHERE active=1
UNION
SELECT contributor,hits
FROM mind_body_soul
WHERE active=1
UNION
SELECT contributor,hits
FROM sound_vision
WHERE active=1
UNION
SELECT contributor,hits
FROM sport_rec
WHERE active=1
UNION
SELECT contributor,hits
FROM travel
WHERE active=1

SET ROWCOUNT 5
SELECT * INTO #myNewTable
FROM #TempItems ORDER BY 1 DESC
SET ROWCOUNT 0
----------------------------------


Hope this works!

 

[vlitim]

That doesn't give me a total though. Need to know for each contributor how many times there articles (as a whole) have been read, and then list the top 5 contributors!

 

[vlitim]

Right I have got it group and adding, but how do I get the records to be displayed in order of hits??!?!? Is it possible to Group and order somehow?!?!?

CREATE PROCEDURE sp_Top5ReadWriters AS

-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON

--Create a temporary table
CREATE TABLE #TempItems
(
ID int IDENTITY,
Hits int,
Contributor int
)

-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (contributor,hits)
SELECT contributor,hits
FROM careers
WHERE active=1
UNION
SELECT contributor,hits
FROM going_out
WHERE active=1
UNION
SELECT contributor,hits
FROM mag
WHERE active=1
UNION
SELECT contributor,hits
FROM mind_body_soul
WHERE active=1
UNION
SELECT contributor,hits
FROM sound_vision
WHERE active=1
UNION
SELECT contributor,hits
FROM sport_rec
WHERE active=1
UNION
SELECT contributor,hits
FROM travel
WHERE active=1

-- Find out the first and last record we want

-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT contributor,sum(hits)
FROM #TempItems
Group by contributor


-- Turn NOCOUNT back OFF
SET NOCOUNT OFF

 

[rajeevnandanmishra]

Use
ORDER BY 2 DESC