Results 1 to 8 of 8

Thread: grouping output

  1. #1
    Join Date
    Nov 2002
    Location
    cornwall
    Posts
    187

    Question 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

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    What is the primary key in this table?. Also post some sample rows.

  3. #3
    Join Date
    Nov 2002
    Location
    cornwall
    Posts
    187
    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)

  4. #4
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    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.

  5. #5
    Join Date
    Nov 2002
    Location
    cornwall
    Posts
    187
    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.

  6. #6
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    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

  7. #7
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    I have not tested it, so if DELETE is not allowed use a cursor to loop through.

  8. #8
    Join Date
    Nov 2002
    Location
    cornwall
    Posts
    187
    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
  •