MISCELLANEOUS FUNCTIONS

MISCELLANEOUS FUNCTIONS

  • Uid
  • User
  • Vsize
  • Rank
  • Dense_rank

a) UID
This will returns the integer value corresponding to the user currently logged in.
Ex:
SQL> select uid from dual;
UID
———-
319

b) USER
This will returns the login’s user name.
Ex:
SQL> select user from dual;
USER
—————-
SAKETH

c) VSIZE
This will returns the number of bytes in the expression.
Ex:
SQL> select vsize(123), vsize(‘computer’), vsize(‘12-jan-90’) from dual;
VSIZE(123) VSIZE(‘COMPUTER’) VSIZE(‘12-JAN-90’)
————- ———————– ———————-
3 8 9
d) RANK
This will give the non-sequential ranking.
Ex:
SQL> select rownum,sal from (select sal from emp order by sal desc);
ROWNUM SAL
———- ———-
1 5000
2 3000
3 3000
4 2975
5 2850
6 2450
7 1600
8 1500
9 1300
10 1250
11 1250
12 1100
13 1000
14 950
15 800

SQL> select rank(2975) within group(order by sal desc) from emp;
RANK(2975)WITHINGROUP(ORDERBYSALDESC)
———————————————————
4

d) DENSE_RANK
This will give the sequential ranking.
Ex:
SQL> select dense_rank(2975) within group(order by sal desc) from emp;

DENSE_RANK(2975)WITHINGROUP(ORDERBYSALDESC)
—————————————————————–
3

More to See at Practice Queries with Examples…

Related posts:

Leave a Reply