csr21

 Profile Answers by csr21 Questions by csr21

• Sep 8th, 2006

 

8]how to delete set of records at a time where all the records having same values , except the Rowid differs?

delete from testsal where rowid in
(select min(rowid) from testsal group by grade
having count(*)>1);

csr21

 Profile Answers by csr21 Questions by csr21

• Sep 8th, 2006

 

6] Why output give error message when i write select stmt, ROWNUM=2 in where clause.?

Reply : ROWNUM will always work for < or <= only, neither for = nor for > nor for >= as ROWNUM always start giving the rows of output result from 1 irrespective of the rownum values in a table. So, when u write ROWNUM=2 for any statement, query will retrive some records say row 'n'  and give it a value as ROWNUM= "1" , thats why it will never give any result for ROWNUM=2.

I hope i m able to explain the concept of rownum, but still if there is any confusion, feel free to ask further

MOHIT SRIVASTAVA

• Oct 3rd, 2006

 

Can you explain by giving some example

Regards

Mohit

chitra

• Oct 4th, 2006

 

Q:) Why output give error message when i write select stmt, ROWNUM=2 in where clause ?

Reply:) Hi Mohit, U try to login thru scott username and lets take table "DEPT" for example. I hope u must be having this table . For eg- it contains total 4 rows.u run the query in the following sequence, u will come to know about the difference and the reason why ROWNUM=2 dint work :

a) select rownum,a.* from dept a;

b) select rownum,a.* from dept a where rownum<3;

c) select rownum,a.* from dept a where rownum=3;

got some idea ??????????

look, when query (b) is run, it will return 2 rows and save them as in output buffer for eg and assign them rownum as 1,2,3,......

but when query (c) is run,it should return the row having rownum=3 according to you,but what happened is that it also tries to return some rows and save them and assigns the returned result as rownum=1 , thats why it can never return any rows for rownum=3 or rownum>3 .
I hope this will clear your doubt ,but if there is still any confusion, feel free to ask .

chitra

• Oct 4th, 2006

 

Q:) Displaying Experience   eg- 5 years 7 months 9 days?

Reply:) I've tried it and I'm able to do it for years and months only , not for days . Here is the query for this


select trunc(months_between(end_date,start_date)/12) || ' years ' ||
trunc(trunc(months_between(end_date,start_date))-
trunc((months_between(end_date,start_date)/12))*12)||' months '
experience,start_date,end_date,employee_id from job_history

S.Senthil Kumar

• Oct 30th, 2006

 

CONNECT BY specifies the relationship between parent rows and child rows of the hierarchy. In a hierarchical query, one expression in condition must be qualified with the PRIOR operator to refer to the parent row. For example,... PRIOR expr = expror... expr = PRIOR expr

seema

• Nov 8th, 2006

 

You can follow this example-

 select floor((trunc(sysdate)  - to_date('01-JAN-01'))/365)  years,
floor(mod((trunc(sysdate)- to_date('01-JAN-01')),365)/30 ) months ,
mod(mod((trunc(sysdate)- to_date('01-JAN-01')),365),30) days from dual;

appu

• Dec 8th, 2006

 

delete from tablename

 where column_name=(select max(column_name) from tablename group by column_names

having count(column1||column2>1);

eg:tablename:student(name,id,age,addr)

Sql> delete from student

where id=(select max(id) from student group by id

having count(id||name)>1);

Bhupendra

• Aug 6th, 2007

 

I have to modify this query because every month doesn't have exactly 30 days.
so in this case the number of days displayed is wrong. We have to take it as
30.42 (which is avg of number of days in a month).
Updated version is as follow

select floor((trunc(sysdate)-to_date('01-jan-01'))/365)||'years' ,floor(mod((trunc(sysdate)-to_date('01-jan-01')),365)/30)||'months',trunc(mod(mod((trunc(sysdate)-to_date('01-jan-01')),365),30.42))||'days'
from dual

pravesh28

 Profile Answers by pravesh28 Questions by pravesh28

• Nov 28th, 2010

 

1 Delete from table_name where ROWID not in ( select

max(rowid) from table group by duplicate_values_field_name);

Muralidhar

• Apr 6th, 2012

 

We delete the duplicate records using level and rownum. The query is

Code
DELETE FROM emp WHERE ROWNUM IN(SELECT MAX(ROWNUM) FROM emp WHERE LEVEL=nth CONNECT BY PRIOR empno>empno GROUP BY LEVEL);