| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: SQL question!
On Thu, 22 Oct 1998 13:50:16 GMT, jpetts_at_celltech.co.uk wrote:
>On Thu, 22 Oct 1998 08:56:56 GMT, jmodic_at_src.si (Jurij Modic) wrote:
>
>
>>SQL> SELECT rownum, y.ven_no, y.yymm, -y.total total FROM
>> 2 (SELECT MIN(x.ven_no) ven_no, x.yymm, -x.total total FROM
>> 3 (SELECT ven_no, SUBSTR(pay_date,1,6) yymm, SUM(pay_amt) total
>> 4 FROM pay
>> 5 WHERE pay_date BETWEEN '19981001' AND '19981031'
>> 6 GROUP BY ven_no, SUBSTR(pay_date,1,6)) x
>> 7 GROUP BY -x.total, x.yymm) y
>> 8 /
>
>This prefixing of a table aliad with a minus sign is something I've
>not come across before. What does it doe? Is it supported by Oracle?
Hi,
I have not prefixed table aliases with minus signs! I was negating the selected values from table columns (not prefixing the aliases!). For more understandable notation I should have used "-1*x.total" instead of "-x.total".
As you probably noticed from my query I used GROUP BY to sort rows returned by inline views as ORDER BY is not allowed inside views (or inline views for that matter). But problem is that GROUP BY allways sorts in the *ascending* order, while Violin's problem demanded *descending* sort. To overcome this problem I performed GROUP BY on negative values, which resulted as using ORDER BY DESCENDING on original values, while in result set I negated the values twice to get the original values (-1*(-1*N)=N).
>James
HTH,
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)
 |
 |