Re: SQL question!

From: Angelo Cavallaro <angelo.cavallaro_at_pcm.bosch.de>
Date: Thu, 22 Oct 1998 14:15:17 +0200
Message-ID: <362F21D5.24B2@pcm.bosch.de>

Hi Violin.

This is a very difficult SQL-Statement, because you can't use rownum in this case.
But there is a solution you could use.

If the pay_date is a character-column it looks like follows:

select count(*) rank_number, inline_a.ven_no, inline_a.pay_month,

       inline_a.total
  from (select ven_no, substr(pay_date,1,6) pay_month,

	       sum(pay_amt) total
	  from pay
	 where substr(pay_date,1,6) = '199810'
	 group by ven_no, substr(pay_date,1,6)) inline_a,
       (select sum(pay_amt) total
	  from pay
	 where substr(pay_date,1,6) = '199810'
	 group by ven_no) inline_b

 where inline_b.total <= inline_a.total  group by inline_a.total, inline_a.ven_no, inline_a.pay_month  order by inline_a.total desc;           

If the pay_date is a date-column it looks like follows:

select count(*) rank_number, inline_a.ven_no, inline_a.pay_month,

       inline_a.total
  from (select ven_no, to_char(pay_date,'YYYYMM') pay_month,

	       sum(pay_amt) total
	  from pay
	 where to_char(pay_date,'YYYYMM') = '199810'
	 group by ven_no, to_char(pay_date,'YYYYMM')) inline_a,
       (select sum(pay_amt) total
	  from pay
	 where to_char(pay_date,'YYYYMM') = '199810'
	 group by ven_no) inline_b

 where inline_b.total >= inline_a.total  group by inline_a.total, inline_a.ven_no, inline_a.pay_month  order by inline_a.total desc;

This are two inline views inline_a and inline_b, which retrieves the sums of pay_amt.
Then inline view inline_b is selected as much as there are sums, which are greater or equal than the sum in inline_a, that means that if the sum in inline_a is e.g. great there are only a few sums in b which are equal or greater and if the sum in a is a little one, there are selected many sums in b which are greater, so that you have many inline_b records for little inline_a sums and a few inline_b records for a big sum in a. Now you count for every inline_a sum the records, which number is depending on inline_b, and you got a ranking. I hope i could explain it a little inmy awesome english.

If this is too difficult for you, you could use a stored function instead to which you give the rowid of the record and it returns the ranking number using PL/SQL.

HTH. Angelito.

PS: I did not try it, but this is the only logic which works

    for rankings.

Violin wrote:
>
> Hello,
> I have a SQL question.
>
> I have a table "PAY" and the colums:
> PAY_NO PAY_DATE VEN_NO PAY_AMT
> -----------------------------------------------------------------
> 000001 19981001 A01 5000.00
> 000002 19981002 A02 3500.50
> 000003 19981005 A06 1820
> 000004 19981006 A02 6300.25
> ::::::::::::::::::;
> I want to have a list order by sum(pay_amt) where pay_date >= '19981001'
> and <= '19981031' and show all rownum, like this:
>
> ROWNUM VEN_NO YYMM TOTAL
> ----------------------------------------------------------
> 1 A02 199810 9800.75
> 2 A01 199810 5000.00
> 3 A06 199810 3000.50
> 4 :::::::::::::::::::::::
>
> So,I can know in '199810',No.1 is 'A02' and sum(pay_amt) is 9800.75,
> No.2 is 'A02' and total is 5000..........
>
> Maybe you know how to create the SELECT statement.
> I'll appreciate for your help.
> Thank you in advance.
> Please Cc to: violin.hsiao_at_mail.pouchen.com.tw
>
> Violin.
Received on Thu Oct 22 1998 - 07:15:17 CDT