Infolinks

Saturday 12 May 2012

SQL-FUNCTIONS1(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

No comments:

Post a Comment