Home » SQL & PL/SQL » SQL & PL/SQL » to get distict values
to get distict values [message #210876] Fri, 22 December 2006 20:47 Go to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
hello orafaq

my query runs well but i want to remove those repeated values means i need distinct rows(no duplication).
can any one please tell me how could i do ?

SQL> ed
Wrote file afiedt.buf

  1  select * from(
  2  select e.*, dense_rank() over(partition by dept_no
  3  order by sal desc nulls last)r from emp1 e)
  4* where r<=3
SQL> /

CLIENT NAME                    SAL    DEPT_NO        EMP JOB      HIREDATE           R
------ ---------------- ---------- ---------- ---------- -------- --------- ----------
C00004 Basu Navindgi        440000         10        500 manager  12-FEB-01          1
C00017 Poonam               390000         10        300 manager                     2
C00011 Ramesh               209000         10       3980 manager  29-FEB-04          3
C00018 Ashish               459902         20        450 manager                     1
C00013 Ritika               451838         20        390  supreme                    2
C00005 Ritika               451838         20        390 supreme                     2
C00013 Ritika               451838         20        390 supreme                     2
C00016 Ishika               367900         20        800 supreme                     3
C00014 Shiva                780000         30        300 manager                     1
C00015 mamoom               260000         30       4980 supreme                     2
C00003 Paramada Jaguste     193700         30       3500 clerk    12-FEB-03          3
C00003 Paramada Jaguste     193700         30       3500 clerk    12-FEB-03          3
C00006 Rukmini              280000         40        500 manager  12-JUN-04          1
C00010 Rupali               126000         40        600 analyst  25-DEC-05          2
C00008 Sushil                25200         40        200 manager  02-JUL-06          3

15 rows selected.


what appendment i have to do on my query?

thanxx
regards
ishika
Re: to get distict values [message #210879 is a reply to message #210876] Fri, 22 December 2006 21:17 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
Hi ishika,

why are you not using the distinct keyword before client .

select distinct client,name,sal,dept_no,emp,job,hiredate
from(
    select e.*, dense_rank() over(partition by dept_no
    order by sal desc nulls last)r from emp1 e)
   where r<=3


I think this will solve your problem.

Bye
Ashu
Re: to get distict values [message #210882 is a reply to message #210876] Fri, 22 December 2006 22:18 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
hello trivedi

thanxx a lot

i will tell u after running this query

bye
ishika
Re: to get distict values [message #210891 is a reply to message #210876] Sat, 23 December 2006 00:27 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
hello,

just curious, what is the logical meaning of duplicate records (all columns having the same value) in EMP1 table. Is there any difference from having just one row with the values in the table.
The column Client seems distinctive to me. Again, is it logically correct if you would have two rows with same Client value, but differing in another column (eg. name, sal, deptno, emp)?
If EMP1 is a view, how is it defined?

bye
jiri
Previous Topic: how to treat char(30) as space-delimited number?
Next Topic: higest salary
Goto Forum:
  


Current Time: Sun Dec 11 08:22:05 CST 2016

Total time taken to generate the page: 0.16874 seconds