Stumped...How to a Rank a 'Summed Column'

[steviep9]

Hi,

My table returns the following:


USERID PPOINTS GPS1 GPS2 TOTAL
---------- ---------- ---------- ------- --------
1 1 1 1 3
1 1 3 1 5
1 1 4 1 5
2 2 5 0 7
2 2 6 0 8
2 2 7 0 9

I want to display:

USERID TOTAL RANK
--------- -------- ------
2 24 1
1 13 2


Unfortunately, we are only license for the Standard Edition
so that eliminates using the RANK function which would resolve my problem.


If the total is not summed, I can get this to work.
However, if it I sum the total column (group by userid) I receive an error.

Here's my code...

select username,sum(o.total) as Total,count(distinct o.userid)
from hockey_offfice_selection ,customer c,hockey_office_selection
where c.userid=o.userid and
sum(o.total) <= sum(s.total) <--error
group by username
order by count(distinct o.userid);


Sample code that I used...

SQL> l
1 SELECT e1.deptno, e1.empno, e1.sal, COUNT(distinct e2.empno)
2 FROM Emp e1, Emp e2
3 WHERE e1.sal <= e2.sal
4 GROUP BY e1.deptno, e1.empno, e1.sal
5 HAVING COUNT(distinct e2.empno) = 7
6* ORDER BY COUNT(distinct e2.empno)
SQL> /


HELP!!!

[mber]

This is what I got, p.l try that:

select t.a, t.b, rownum
from (select a, sum(b) b from test_a group by a order by b desc) t;

A B ROWNUM
----- --------- ---------
2 24 1
1 13 2