| Giridhar,...please reply.... [message #373892] |
Wed, 16 May 2001 09:35  |
ramana
Messages: 51
Registered: December 2000
|
Member |
|
|
1).I have table with duplicate records.
I want to retreive only duplicate records.
can u please reply how to it?
2)I have a table with 50 records.I want to retrieve only 4,5,6,7,8 records.
how to do it?
thanx in advance..
|
|
|
|
| Re: Giridhar,...please reply.... [message #373899 is a reply to message #373892] |
Wed, 16 May 2001 13:08  |
spdevalla
Messages: 7
Registered: December 2000
Location: Rhode Island
|
Junior Member |
|
|
select field1,count(*) from table
group by field1
having count(*) > 1;
or if you are sure you want to retrieve 4,5,6,7 and 8th record you can use the rownum between 4 and 8 this might wirk.
|
|
|
|
| Re: Giridhar,...please reply.... [message #373905 is a reply to message #373892] |
Thu, 17 May 2001 01:19 |
GIRIDHAR KODAKALLA
Messages: 92
Registered: May 2001
|
Member |
|
|
Hai Ramana,
Good morning.
1) For the first question, this is the query.
Suppose I have a table TEST,
SQL> select * from test;
MYDATE NAME1 NAME2 NAME3
--------- -------------------- -------------------- --------------------
02-MAR-99 A B C
03-AUG-00 W R E
18-JUL-00 WEWE GHH FDFD
17-MAY-00 GIRI DHAR MAY
02-MAR-99 A B C
And this query has to return me the duplicate rows in that table.
SQL> select * from test a where rowid not in (select max(rowid) from test b
2 where a.mydate = b.mydate and a.name1=b.name1 and a.name2=b.name2 and a.name3=b.name3);
IF any one can suggest a better query, I would be thankful as practically it may be
Difficult to join all the columns. If we have any primary key, then I don’t think we have that
Problem of joining all the columns, but here I assume that it is a plain table without any primary key
And this is the output.
MYDATE NAME1 NAME2 NAME3
--------- -------------------- -------------------- --------------------
02-MAR-99 A B C
2) For the second question, the query is as follows:-
Suppose if you want to retrive the 3rd record in the table,
select * from test where rownum <= 3
minus
select * from test where rownum < 3;
Change the number 3 with the number of record (4th or 5th ) you want to retrieve from the table.
Hope This Helps.
Cheers,
Giridhar Kodakalla
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
