Re: Newbie SQL question (Top 10 values)

From: Marcus Williford <marcus_at_eola.ao.net>
Date: 1996/01/13
Message-ID: <4d7c0q$tlg_at_news.accessorl.net>#1/1

Rob Weeks (rweeks_at_kodak.com) wrote:
: How in the heck do I write a query that either returns the top 10% or
: top ten values of a specific field (sales information for instance)? In
: my MSAccess days, there was a Top 10 values wizard thingy to do the same
: type of thing. Many thanks in advance for any help that you can give me.

I am new with this sql stuff, but I will try to answer you question. I think that you have to do this is pl/sql, so use

as an example

---

ACCEPT p_ord_id Prompt 'Please enter the order ID:'

DECLARE

v_order_id	s_item.ord_id%type :&p_ord_id;
v_total         NUMBER(11,2) :=0;

CURSOR item_cursor IS
	select total, id
	from s_items
	order total desc;
BEGIN
	OPEN item_cursor;
	LOOP
		FETCH item_cursor INTO v_total, v_order_id;
		EXIT when item_cursor%ROWCOUNT > 10 
			OR item_cursor%NOTFOUND;
		/*now you have only a max of 10 top totals*/
		/*write each to a temp table*/
		INSERT INTO temp (total, id)
		values(v_total, v_order_id);
	END LOOP;
	CLOSE item_cursor;
	COMMIT;

END;
/

---

I am a beginner at this, so if the experts see a better way, then please follow this up.

Hope that helps..

marcus
marcus_at_ao.net Received on Sat Jan 13 1996 - 00:00:00 CET