Re: Need help on Oracle SQL query

From: Matthew MacFarland <dril_at_quip.com>
Date: Tue, 5 May 1998 12:14:32 -0500
Message-ID: <6inhb1$jbe$1@uuneo.neosoft.com>

Hello BK,

I don't know way to do it in straight SQL but you could use a PL/SQL procedure to pick the top 10 and write the results to a table that you could then select from
Something like:

PROCEDURE TopTenLoc
IS

    --Cursor of all location ticket counts     CURSOR c_LocCnts IS
    SELECT location, Count(ticket_id) as num_tickets     FROM trouble_ticket
    GROUP BY location
    HAVING Count(ticket_id) > 1
    ORDER BY num_tickets DESC;

    --Keep track of row count
    v_Rows NUMBER := 0;

BEGIN
    FOR v_Data IN c_LocCnts LOOP

            EXIT WHEN v_Rows > 10;
            v_Rows := v_Rows + 1;
            INSERT INTO top_ten (location, num_tickets)
                VALUES  (v_Data.location, v_Data.num_tickets);
     END LOOP;

    COMMIT;
END TopTenLoc;

Then

    SELECT *
    FROM top_ten
    ORDER BY num_tickets;
to see the results.

Matthew MacFarland

BK wrote in message <354A55A7.331DAA1_at_watchmark.com>...
>Hi,
>
>I got a trouble ticket table with the following columns:
> Ticket_Id,
> Location,
> and some ticket info columns.
>
>I need to do a query that give me top ten locations with the most
>trouble ticket counts.
>
>I try this query
> select * from trouble_ticket where location =
> ( select location from trouble_ticket group by location having
>count(*) > 1);
>
>but this only provide me with locations that exceeded the threshold
>value for trouble count.
>
>Any idea?
>
>
>BK
>
Received on Tue May 05 1998 - 12:14:32 CDT