Re: Newbie SQL question (Top 10 values)

From: Jennifer Blair <BTT_at_ix.netcom.com>
Date: 1996/01/17
Message-ID: <4dihhv$eht_at_ixnews8.ix.netcom.com>#1/1


In <4dg8hc$1bn_at_crissy.ge.com> Donna Kray <kray.donna_at_mlink.motors.ge.com> writes:

>
>mudhens_at_cyberramp.net (RAC) wrote:
>>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)?
>>
>>You can get the top ten values by (1) sorting in descending order the
>>field that you want to see and (2) adding an additional where clause
>>stating "and rownum < 11" For example, you query might look like
>>
>>select sales
>>from table_name
>>where rownum < 11
>>order by sales desc
>>
>>Hope this helps.
>>
>=======================================================================
>ROWNUM is assigned to retreived rows *before* any ordering is done in
 the
>ORDER BY clause. Thus, this query will not return what you want
 reliably.
>
>I don't, however, have any other solution, just a caveat.
>
>DL Kray
>
>
>
>
>
>
>
>
>
>
>
>
>
>
You're searching for a way to get the top ten salespersons?

How about using a little PL/SQL?

/* Routine to Find the Top 10 Salespersons Using %Rowtype */

DECLARE

	cursor c_salespersons is
		select last_name, first_name, ytd_sales
		from sales
		where ytd_sales is not null
		order by ytd_sales desc;
	sales_row c_salespersons%rowtype;
BEGIN
	open c_salespersons;
	for i in 1..10 loop
		fetch c_salespersons into sales_row;
		exit when c-salespersons%notfound;
		-- insert results into a table created
		-- to track the results
		insert into results(message, col1)
		values
		sales_row.last_name||', '||sales_row.first_name,
		sales_row.ytd_sales);
	end loop;
	commit;
	close c_salespersons;

END; The table called results would be created as follows:
	create table results
	(col1 number,
         col2 number,
 	 message varchar2(80));

It can be used for other purposes, as well.

Of course, I don't know what your sales table looks like, or the column names you're using. This is just an example.

If you have questions, write back.

Good luck!

Jennifer Blair, President
Blair Technical Training
Specializing in On-Site Unix & Oracle Training

P.S. We teach classes in PL/SQL. Received on Wed Jan 17 1996 - 00:00:00 CET

Original text of this message