-
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!!!
-
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
