| Rank without analytical function [message #606137] |
Mon, 20 January 2014 05:38  |
 |
rishwinger
Messages: 132
Registered: November 2011
|
Senior Member |
|
|
Hello Experts,
Consider SCOTT.EMP Table
SQL> SELECT deptno,
hiredate,
row_number() over(partition BY deptno order by hiredate) AS rank
FROM emp;
DEPTNO HIREDATE RANK
---------- --------- ----------
10 09-JUN-81 1
10 17-NOV-81 2
10 23-JAN-82 3
20 17-DEC-80 1
20 02-APR-81 2
20 03-DEC-81 3
20 19-APR-87 4
20 23-MAY-87 5
30 20-FEB-81 1
30 22-FEB-81 2
30 01-MAY-81 3
30 08-SEP-81 4
30 28-SEP-81 5
30 03-DEC-81 6
14 rows selected.
I need above output without using analytical function, Please advise
Regards
|
|
|
|
| Re: Rank without analytical function [message #606138 is a reply to message #606137] |
Mon, 20 January 2014 05:45  |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You need to tell us why you're trying to avoid using the tool that's specifically designed for this job, since the reasons will probably affect what subsitutes are acceptable.
I can't think of a pure sql alternative off the top of my head.
|
|
|
|
| Re: Rank without analytical function [message #606139 is a reply to message #606138] |
Mon, 20 January 2014 05:48 |
|
rishwinger
Messages: 132
Registered: November 2011
|
Senior Member |
|
|
Well my app dev is having trouble using partition in JPA query,
guys thx anyways i got it
SQL> SELECT d1.deptno,
2 d1.hiredate,
3 row_number() over(partition BY deptno order by hiredate DESC) AS rank
4 (SELECT COUNT(*)+1
5 FROM emp d2
6 WHERE d1.deptno=d2.deptno
7 AND d1.hiredate<d2.hiredate
8 ) as test
9 FROM emp d1;
DEPTNO HIREDATE RANK TEST
---------- --------- ---------- ----------
10 23-JAN-82 1 1
10 17-NOV-81 2 2
10 09-JUN-81 3 3
20 23-MAY-87 1 1
20 19-APR-87 2 2
20 03-DEC-81 3 3
20 02-APR-81 4 4
20 17-DEC-80 5 5
30 03-DEC-81 1 1
30 28-SEP-81 2 2
30 08-SEP-81 3 3
DEPTNO HIREDATE RANK TEST
---------- --------- ---------- ----------
30 01-MAY-81 4 4
30 22-FEB-81 5 5
30 20-FEB-81 6 6
14 rows selected.
[Updated on: Mon, 20 January 2014 05:50] Report message to a moderator |
|
|
|
|
|
| Re: Rank without analytical function [message #606143 is a reply to message #606139] |
Mon, 20 January 2014 06:04 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
rishwinger wrote on Mon, 20 January 2014 17:18
Well my app dev is having trouble using partition in JPA query,
guys thx anyways i got it
SQL> SELECT d1.deptno,
2 d1.hiredate,
3 row_number() over(partition BY deptno order by hiredate DESC) AS rank
4 (SELECT COUNT(*)+1
5 FROM emp d2
6 WHERE d1.deptno=d2.deptno
7 AND d1.hiredate<d2.hiredate
8 ) as test
9 FROM emp d1;
And more trouble is round the corner. You are accessing the tables twice unnecessarily. So on what cost you want to avoid analytic and use this performance degrading query?
|
|
|
|
|
|
|
|
| Re: Rank without analytical function [message #606147 is a reply to message #606139] |
Mon, 20 January 2014 06:13 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
rishwinger wrote on Mon, 20 January 2014 06:48
guys thx anyways i got it
It is not clear how you want to rank equal values. Your solution doesn't rank rows same way as ROW_NUMBER numbers them. It mimics RANK, not ROW_NUMBER:
SQL> update emp
2 set hiredate = date '2013-01-01'
3 where deptno = 20
4 and rownum <= 2
5 /
2 rows updated.
SQL> update emp
2 set hiredate = date '2014-01-01'
3 where deptno = 20
4 and hiredate != date '2013-01-01'
5 and rownum <= 2
6 /
2 rows updated.
SQL> SELECT d1.deptno,
2 d1.hiredate,
3 row_number() over(partition BY deptno order by hiredate DESC) AS your_rank,
4 rank() over(partition BY deptno order by hiredate DESC) AS actual_rank,
5 (
6 SELECT COUNT(*) + 1
7 FROM emp d2
8 WHERE d1.deptno=d2.deptno
9 AND d1.hiredate<d2.hiredate
10 ) as test
11 FROM emp d1
12 /
DEPTNO HIREDATE YOUR_RANK ACTUAL_RANK TEST
---------- --------- ---------- ----------- ----------
10 23-JAN-82 1 1 1
10 17-NOV-81 2 2 2
10 09-JUN-81 3 3 3
20 01-JAN-14 1 1 1
20 01-JAN-14 2 1 1
20 01-JAN-13 3 3 3
20 01-JAN-13 4 3 3
20 03-DEC-81 5 5 5
30 03-DEC-81 1 1 1
30 28-SEP-81 2 2 2
30 08-SEP-81 3 3 3
DEPTNO HIREDATE YOUR_RANK ACTUAL_RANK TEST
---------- --------- ---------- ----------- ----------
30 01-MAY-81 4 4 4
30 22-FEB-81 5 5 5
30 20-FEB-81 6 6 6
14 rows selected.
SQL> rollback
2 /
Rollback complete.
SQL>
Add ROWID, if you want to mimic ROW_NUMBER:
SQL> update emp
2 set hiredate = date '2013-01-01'
3 where deptno = 20
4 and rownum <= 2
5 /
2 rows updated.
SQL> update emp
2 set hiredate = date '2014-01-01'
3 where deptno = 20
4 and hiredate != date '2013-01-01'
5 and rownum <= 2
6 /
2 rows updated.
SQL> SELECT d1.deptno,
2 d1.hiredate,
3 row_number() over(partition BY deptno order by hiredate DESC) AS your_rank,
4 rank() over(partition BY deptno order by hiredate DESC) AS actual_rank,
5 (
6 SELECT COUNT(*) + 1
7 FROM emp d2
8 WHERE d1.deptno = d2.deptno
9 AND d1.hiredate < d2.hiredate
10 OR (
11 d1.hiredate = d2.hiredate
12 AND
13 d1.rowid < d2.rowid
14 )
15 ) as test
16 FROM emp d1
17 order by d1.deptno,
18 test
19 /
DEPTNO HIREDATE YOUR_RANK ACTUAL_RANK TEST
---------- --------- ---------- ----------- ----------
10 23-JAN-82 1 1 1
10 17-NOV-81 2 2 2
10 09-JUN-81 3 3 3
20 01-JAN-14 2 1 1
20 01-JAN-14 1 1 2
20 01-JAN-13 3 3 3
20 01-JAN-13 4 3 4
20 03-DEC-81 5 5 5
30 03-DEC-81 1 1 2
30 28-SEP-81 2 2 2
30 08-SEP-81 3 3 3
DEPTNO HIREDATE YOUR_RANK ACTUAL_RANK TEST
---------- --------- ---------- ----------- ----------
30 01-MAY-81 4 4 4
30 22-FEB-81 5 5 5
30 20-FEB-81 6 6 6
14 rows selected.
SQL> rollback
2 /
Rollback complete.
SQL>
SY.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
