-
grouping output
Hi,
i need to analyse some data (the SQL will go into a view and then be accessed by a webpage) and cannot get the formatting correct.
We have a single table of data and i am concerned with only two fields.
1 - Office
2 - website
i want a list of the top ten websites per office. meaning, if we have 10 offices (based on the count of the website field per office)i would expect 100 rows of data to be returned.
can someone please enlighten me on what code i might need to use? I have tried a number of combinations and cannot get anything useful.
I would like to have the SQL run as one block - i have thought about hardcoding in the office names and running 10 SQL queries in the view but this will mean an overhead if we gain/lose offices.
TIA
fatherjack
-
What is the primary key in this table?. Also post some sample rows.
-
oops, i lied, there are three fields
row1
CDCTRU\PUBLICPC
www.bbc.co.uk
352
row1
CDCTRU\PUBLICPC
www.autodirectinsuranceservices.co.uk
96
row3
CDCTRU\PUBLICPC
www.tescofinance.com
93
row4
CDCTRU\PUBLICPC
www.eaglestar.co.uk
73
there is no primary key. all fields are varchar (the number as field 3 is a count on field 1)
-
You can write a table valued function which does this.
create a temp table to save distinct office values.
loop through the temp table and populate return table.
finally return the table.
-
sorry to be a pain but i have no idea how that might be put in place.
I can see the logic but my SQL experience is largely limited to data recovery to ASP pages. Could you post any skeleton SQL structure please?
Many thanks
FatherJack
Last edited by fatherjack; 08-15-2003 at 11:14 AM.
-
Something of this sort should work.
CREATE FUNCTION topten
RETURNS @toptentable TABLE (office varchar(255), website varchar(255), hits int)
AS
BEGIN
DECLARE @officelist TABLE (office varchar(255))
DECLARE @office varchar(255)
INSERT @officelist
SELECT distinct office
FROM yourtable
while (select count(*) from @officelist) > 0
begin
select @office = top 1 office from @officelist
insert into @toptentable (office, website, hits)
select top 10 office, website, hits
from yourtable
where office = @office
order by hits desc
delete @officelist where office = @office
end
RETURN
END
-
I have not tested it, so if DELETE is not allowed use a cursor to loop through.
-
thank you very much, that is very helpful. i cannot test it however for a few days as i am working in another office. i will post my success later this week.
thanks
fatherjack
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|