|
-
Group by
Hi There,
I have 2 tables as below:
TABLE1
FirstnameSurname DOB File no
A Smith 1/04/1980 122
A Smith 1/04/1980 200
B Adam 2/04/1988 110
C Smith 12/04/1985 250
D Miles 12/12/1945 300
D Miles 12/12/1945 400
TABLE2
File no Order date Delivery date
122 14/10/1950 14/10/1950
200 25/01/1960 2/11/1990
110 25/04/1999 25/04/1999
250 25/01/1980 11/11/1991
300 22/01/1978 23/04/1987
400 14/07/1974 25/01/1982
I want to get the cases where we have same surname,first name& DOB but only one of such file has the same order date & delivery date. So the results should look something like:
File A FileB
122 200
Any help will be greatly appreciated
-
... and the specific question is?
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
I want to find the case such as
File A FileB
122 200
m not sure how to get this result
-
Pls try this one ...this will give the correct result vertically.if you use pl\sql you will get the exact result.
select tab2.fileno
from ( select * from table2 where orddate=del_date ) tab1 right outer join
( select fileno
from table1
where firstname in ( select firstname
from table1
group by firstname,surname,dob
having count(fileno)>1)
) tab2 on (tab1.fileno=tab2.fileno)
-
 Originally Posted by aryasen
this will give the correct result vertically.
really?
lets see...
Code:
SQL>
SQL> create table TABLE1
2 (
3 Firstname varchar2(10),
4 Surname varchar2(10),
5 DOB date,
6 File_no number);
Table created.
SQL> insert into TABLE1 values('A', 'Smith', to_date('01/04/1980','dd/mm/yyyy'), 122);
1 row created.
SQL> insert into TABLE1 values('A', 'Smith', to_date('01/04/1980','dd/mm/yyyy'), 200);
1 row created.
SQL> insert into TABLE1 values('B', 'Adam', to_date('02/04/1988','dd/mm/yyyy'), 110);
1 row created.
SQL> insert into TABLE1 values('C', 'Smith', to_date('12/04/1985','dd/mm/yyyy'), 250);
1 row created.
SQL> insert into TABLE1 values('D', 'Miles', to_date('12/12/1945','dd/mm/yyyy'), 300);
1 row created.
SQL> insert into TABLE1 values('D', 'Miles', to_date('12/12/1945','dd/mm/yyyy'), 400);
1 row created.
SQL> commit;
Commit complete.
SQL> create table TABLE2
2 (
3 File_no number,
4 Order_date date,
5 Delivery_date date);
Table created.
SQL> insert into TABLE2 values(122, to_date('14/10/1950','dd/mm/yyyy'), to_date('14/10/1950','dd/mm/
yyyy'));
1 row created.
SQL> insert into TABLE2 values(200, to_date('25/01/1960','dd/mm/yyyy'), to_date('02/11/1990','dd/mm/
yyyy'));
1 row created.
SQL> insert into TABLE2 values(110, to_date('25/04/1999','dd/mm/yyyy'), to_date('25/04/1999','dd/mm/
yyyy'));
1 row created.
SQL> insert into TABLE2 values(250, to_date('25/01/1980','dd/mm/yyyy'), to_date('11/11/1991','dd/mm/
yyyy'));
1 row created.
SQL> insert into TABLE2 values(300, to_date('22/01/1978','dd/mm/yyyy'), to_date('23/04/1987','dd/mm/
yyyy'));
1 row created.
SQL> insert into TABLE2 values(400, to_date('14/07/1974','dd/mm/yyyy'), to_date('25/01/1982','dd/mm/
yyyy'));
1 row created.
SQL> commit;
Commit complete.
SQL> select tab2.fileno
2 from ( select * from table2 where orddate=del_date ) tab1 right outer join
3 ( select fileno
4 from table1
5 where firstname in ( select firstname
6 from table1
7 group by firstname,surname,dob
8 having count(fileno)>1)
9 ) tab2 on (tab1.fileno=tab2.fileno)
10 ;
( select fileno
*
ERROR at line 3:
ORA-00904: "FILENO": invalid identifier
SQL>
leason learned? ... you have to test your stuff, people asking question already have their problems; please do not add new ones.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Originally Posted by PAVB
really?
lets see...
Code:
SQL>
SQL> create table TABLE1
2 (
3 Firstname varchar2(10),
4 Surname varchar2(10),
5 DOB date,
6 File_no number);
Table created.
SQL> insert into TABLE1 values('A', 'Smith', to_date('01/04/1980','dd/mm/yyyy'), 122);
1 row created.
SQL> insert into TABLE1 values('A', 'Smith', to_date('01/04/1980','dd/mm/yyyy'), 200);
1 row created.
SQL> insert into TABLE1 values('B', 'Adam', to_date('02/04/1988','dd/mm/yyyy'), 110);
1 row created.
SQL> insert into TABLE1 values('C', 'Smith', to_date('12/04/1985','dd/mm/yyyy'), 250);
1 row created.
SQL> insert into TABLE1 values('D', 'Miles', to_date('12/12/1945','dd/mm/yyyy'), 300);
1 row created.
SQL> insert into TABLE1 values('D', 'Miles', to_date('12/12/1945','dd/mm/yyyy'), 400);
1 row created.
SQL> commit;
Commit complete.
SQL> create table TABLE2
2 (
3 File_no number,
4 Order_date date,
5 Delivery_date date);
Table created.
SQL> insert into TABLE2 values(122, to_date('14/10/1950','dd/mm/yyyy'), to_date('14/10/1950','dd/mm/
yyyy'));
1 row created.
SQL> insert into TABLE2 values(200, to_date('25/01/1960','dd/mm/yyyy'), to_date('02/11/1990','dd/mm/
yyyy'));
1 row created.
SQL> insert into TABLE2 values(110, to_date('25/04/1999','dd/mm/yyyy'), to_date('25/04/1999','dd/mm/
yyyy'));
1 row created.
SQL> insert into TABLE2 values(250, to_date('25/01/1980','dd/mm/yyyy'), to_date('11/11/1991','dd/mm/
yyyy'));
1 row created.
SQL> insert into TABLE2 values(300, to_date('22/01/1978','dd/mm/yyyy'), to_date('23/04/1987','dd/mm/
yyyy'));
1 row created.
SQL> insert into TABLE2 values(400, to_date('14/07/1974','dd/mm/yyyy'), to_date('25/01/1982','dd/mm/
yyyy'));
1 row created.
SQL> commit;
Commit complete.
SQL> select tab2.fileno
2 from ( select * from table2 where orddate=del_date ) tab1 right outer join
3 ( select fileno
4 from table1
5 where firstname in ( select firstname
6 from table1
7 group by firstname,surname,dob
8 having count(fileno)>1)
9 ) tab2 on (tab1.fileno=tab2.fileno)
10 ;
( select fileno
*
ERROR at line 3:
ORA-00904: "FILENO": invalid identifier
SQL>
leason learned? ... you have to test your stuff, people asking question already have their problems; please do not add new ones.
============
Pls change the column names as
1)
SQL> create table TABLE1
2 (
3 firstname varchar2(10),
4 surname varchar2(10),
5 dob date,
6 fileno number);
2)
SQL> create table TABLE2
2 (
3 File_no number,
4 orddate date,
5 del_date date);
then you try that query.. u will get the vertical result 
-
select dt1.fileno from dt1,
(select firstname,surname,dob
from table1 a
where a.fileno in (select b.file_no from table2 b where b.delivery_date = b.order_date)
group by firstname,surname,dob
having count(fileno)>1) dt2
where dt1.firstname = dt2.firstname
and dt1.surname=dt2.surname
and dt1.dob = dt2.dob
Please substitute your columns in the query
-
Originally Posted by sat_mellow
select dt1.fileno from dt1,
(select firstname,surname,dob
from table1 a
where a.fileno in (select b.file_no from table2 b where b.delivery_date = b.order_date)
group by firstname,surname,dob
having count(fileno)>1) dt2
where dt1.firstname = dt2.firstname
and dt1.surname=dt2.surname
and dt1.dob = dt2.dob
Please substitute your columns in the query
===============================
select tab2.File_no
from ( select * from table2 where Order_date=Delivery_date) tab1 right outer join
( select File_no
from table1
where Firstname in ( select Firstname
from table1
group by Firstname,Surname,dob
having count(File_no)>1)
) tab2 on (tab1.File_no=tab2.File_no)
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote