SQL Query
Venkat, February 03, 2003 - 9:58 am UTC
Thank you so much Tom.
God...give me 10% of Tom's knowledge.
Perfect, thanks Tom
Marc, February 03, 2003 - 5:56 pm UTC
Sorry for asking an irrelevant question here...
Riaz Shahid, August 05, 2003 - 8:22 am UTC
Consider the following:
cr@STARR.LHR> ed
Wrote file afiedt.buf
1 select * from (
2 select nic_no NIC,reg_no,name,trim(address_1) "Address"
3 from gstt01 a
4 where nic_no in
5 (select nic_no from (
6 (select count(*),nic_no from gstt01
7 where trim(nic_no)!='0'
8 group by nic_no
9 having count(*)>1
10 )
11 )
12 )
13 order by 1
14 )
15* where rownum<10
NIC REG_NO Address
============= ============= ==============================
093-71-045527 0309520091628 410-FIRST FLOOR CLOTH MARKET
0309520086691 410-FIRST FLOOR CLOTH MARKET
101-44-637033 1200581002119 38-E, BLOCK-6, PECHS,
1200840003446 38-E, BLOCK-6, PECHS,
101-47-075162 0701980505219 PLOT # 76, STREET # 6, SECTOR 1- 1100980552364 PLOT # 76, SECTOR-6, SECTOR-I-101-52-486192 0701980125473 XX
0701220204919 PLOT # 82, STREET # 10, I-9/2
101-54-014392 0701170100364 H.NO. 23, GALI NO. 20
9 rows selected.
Elapsed: 00:00:51.97
Statistics
==========================================================
6 recursive calls
132 db block gets
23294 consistent gets
21218 physical reads
0 redo size
1278 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
7 sorts (memory)
1 sorts (disk)
9 rows processed
(I've put "where rownum<10" to show you just a set of whole resut set).
I want those records (out of those as shown above) who has:
(1) the same NIC_No
(2) different reg_no
and
(3) Whose first 10 characters of column address_1 are equal.
Can you help me regarding that ???
Riaz
August 05, 2003 - 12:57 pm UTC
1) the same nic_no as what?
2) a different reg_no from what?
3) are equal to what?
So Sorry....
Riaz, August 07, 2003 - 4:29 am UTC
I am really sorry for not providing the complete information...really sorry for wasting your time. I solved that ptoblem. But now i am facing problem with another query...consider:
cr@STARR.LHR> create table test (reg_no varchar2(13), address varchar2(60),oper_sts char(1),reg_date date);
Table created.
Elapsed: 00:00:00.46
cr@STARR.LHR> ED
Wrote file afiedt.buf
1* insert into test values('1111111111111','HELLO','1',SYSDATE-10)
cr@STARR.LHR> /
1 row created.
Elapsed: 00:00:00.25
cr@STARR.LHR> ED
Wrote file afiedt.buf
1* insert into test values('2222222222222','HELLO','1',SYSDATE-15)
cr@STARR.LHR> /
1 row created.
Elapsed: 00:00:00.09
cr@STARR.LHR> ED
Wrote file afiedt.buf
1* insert into test values('3333333333333','I AM HERE','1',SYSDATE-15)
cr@STARR.LHR> /
1 row created.
Elapsed: 00:00:00.09
cr@STARR.LHR> ed
Wrote file afiedt.buf
1* insert into test values('4444444444444','I AM NOT HERE','1',SYSDATE-15)
cr@STARR.LHR> /
1 row created.
Elapsed: 00:00:00.25
cr@STARR.LHR> COMMIT;
Commit complete.
Elapsed: 00:00:00.25
cr@STARR.LHR> SELECT * FROM TEST;
REG_NO ADDRESS Oper_STS REG_DATE
1111111111111 HELLO 1 28-JUL-03 2222222222222 HELLO 1 23-JUL-03 3333333333333 I AM HERE 1 23-JUL-03 4444444444444 I AM NOT HERE 1 23-JUL-03
Elapsed: 00:00:00.97
I need only those records who have duplicate address (first 10 characters matching).i.e; i need the following:
REG_NO ADDRESS Oper_STS REG_DATE
1111111111111 HELLO 1 28-JUL-03 2222222222222 HELLO 1 23-JUL-03
Can you help me ???
SQL QUERY
Khan MD, August 08, 2003 - 4:54 am UTC
Try This,
create table test (reg_no varchar2(13), address
varchar2(60),oper_sts char(1),reg_date date);
select a.* from test a , test b
where substr(a.address,1,10) = substr(b.address,1,10)
and a.rowid <> b.rowid;
select * from test a
where exists (select 1 from test
where substr(a.address,1,10) = substr(address,1,10)
group by substr(address,1,10)
having count(substr(address,1,10)) > 1);