Home » SQL & PL/SQL » SQL & PL/SQL » Joining Tables and getting the record with the least/min date value (Oracle 10g)
Joining Tables and getting the record with the least/min date value [message #574932] Wed, 16 January 2013 22:29 Go to next message
schinn
Messages: 5
Registered: January 2013
Junior Member
Hello Friends..
We have two tables tab1 and tab2 same structures and empid and id are keys.

We need to query for records that doesnt exist in tab2 and exist in tab1 based on keys above and then insert into t2.
And also we have a date column in tab1...
if we have two records..that match empid and id and if dates are different we have to take one record with the least date like min(Date) and insert that one record in to tab2.

any help on this would be appreciated
Re: Joining Tables and getting the record with the least/min date value [message #574933 is a reply to message #574932] Wed, 16 January 2013 22:34 Go to previous messageGo to next message
BlackSwan
Messages: 22911
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Joining Tables and getting the record with the least/min date value [message #574936 is a reply to message #574933] Wed, 16 January 2013 22:44 Go to previous messageGo to next message
schinn
Messages: 5
Registered: January 2013
Junior Member
Thanks...This is what i did so far...

SELECT t1.* FROM
tab1 t1
LEFT JOIN
tab2 t2
ON t2.empid = t1.empid and t2.id = t1.id
WHERE t2.empid IS NULL and t1.ACTIND = 1

i get the records but i want the column only with the least date.
Suppose i have recors with 1,1,2012-01-01 and 1,1,2011-01-01 i want to pick only the 2011 record.
Re: Joining Tables and getting the record with the least/min date value [message #574937 is a reply to message #574936] Wed, 16 January 2013 22:52 Go to previous messageGo to next message
BlackSwan
Messages: 22911
Registered: January 2009
Senior Member
realize that we don't have your tables or data; therefore we can't run, test or improve your posted SQL

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Joining Tables and getting the record with the least/min date value [message #574941 is a reply to message #574936] Wed, 16 January 2013 23:48 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Below test case may not be the best possible solution to your problem but it surely works

SQL> create table emp1 as select * from emp;

Table created.

SQL> insert into emp1 (empno,ename,job,hiredate,sal,comm,deptno) values(009,'ris
hwinger','Agent',sysdate-1,20000,0,20);

1 row created.

SQL> insert into emp1 (empno,ename,job,hiredate,sal,comm,deptno) values(007,'ris
hwinger','DBA',sysdate,20000,0,10);

1 row created.

SQL> commit;

Commit complete.

SQL> select a.empno,a.ename,a.job,a.hiredate from emp1 a,emp b where a.empno=b.e
mpno(+) and b.empno is null;

     EMPNO ENAME      JOB       HIREDATE
---------- ---------- --------- ---------
         9 rishwinger Agent     16-JAN-13
         7 rishwinger DBA       17-JAN-13

SQL> select a.empno,a.ename,a.job,a.hiredate from emp1 a,emp b where a.empno=b.e
mpno(+) and b.empno is null and a.hiredate=(select min(a.hiredate) from emp1 a,e
mp b where a.empno=b.empno(+) and b.empno is null);

     EMPNO ENAME      JOB       HIREDATE
---------- ---------- --------- ---------
         9 rishwinger Agent     16-JAN-13
Re: Joining Tables and getting the record with the least/min date value [message #574942 is a reply to message #574941] Thu, 17 January 2013 00:01 Go to previous messageGo to next message
schinn
Messages: 5
Registered: January 2013
Junior Member
rishwinger
...Thanks Very Much For Your Help!
Looks like it's going to work will test it and keep you posted.

Thanks Again..
Re: Joining Tables and getting the record with the least/min date value [message #574947 is a reply to message #574942] Thu, 17 January 2013 00:48 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
insert into tab2
select * from tab1
where key in (select key from tab1 minus select key from tab2)
/

Regards
Michel
Re: Joining Tables and getting the record with the least/min date value [message #574985 is a reply to message #574947] Thu, 17 January 2013 06:24 Go to previous messageGo to next message
schinn
Messages: 5
Registered: January 2013
Junior Member
Michel,I should only get the record with the least date in table1.

Thanks For Your Help!
Re: Joining Tables and getting the record with the least/min date value [message #574987 is a reply to message #574985] Thu, 17 January 2013 07:34 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Re: Joining Tables and getting the record with the least/min date value [message #574991 is a reply to message #574987] Thu, 17 January 2013 08:57 Go to previous messageGo to next message
schinn
Messages: 5
Registered: January 2013
Junior Member
Michel,
Hoping this helps..

CREATE TABLE tab1
(
empid integer
id integerNUMBER(18) NOT NULL,
sysdat timestamp

CREATE TABLE tab2
(
empid integer
id integerNUMBER(18) NOT NULL,
sysdat timestamp


Insert into tab1(

1555637 6366 7/12/2012 1:13:14.240000 PM
1555637 6366 7/12/2012 1:17:44.927000 PM
1555637 6366 7/12/2012 1:17:01.873000 PM
1555637 6366 7/12/2012 1:13:28.810000 PM

Select * from tab1 --will give me above results.

Assume tab2 doesnt have record with empid 1555637 and id 6366.
when i do a query checking if the record exists in tab2 if it doesnt i should get
one record with the least date which is


1555637 6366 7/12/2012 1:13:14.240000 PM

Thanks Again
Re: Joining Tables and getting the record with the least/min date value [message #574992 is a reply to message #574991] Thu, 17 January 2013 09:03 Go to previous messageGo to next message
BlackSwan
Messages: 22911
Registered: January 2009
Senior Member
>Hoping this helps..
does not help since it is not valid DDL

[Updated on: Thu, 17 January 2013 09:06]

Report message to a moderator

Re: Joining Tables and getting the record with the least/min date value [message #574993 is a reply to message #574932] Thu, 17 January 2013 09:27 Go to previous message
sgollapudi
Messages: 9
Registered: January 2013
Location: HYDERABAD
Junior Member
Please try with

INSERT INTO tab2 
SELECT empid, 
       id, 
       Min(sysdat) 
FROM   tab1 
WHERE  KEY IN (SELECT KEY 
               FROM   tab1 
               MINUS 
               SELECT KEY 
               FROM   tab2) 
GROUP  BY empid, 
          id; 

Regards,
sgollapudi

*BlackSwan added {code} tags. Please do so yourself in the future.

[Updated on: Thu, 17 January 2013 09:37] by Moderator

Report message to a moderator

Previous Topic: Create Gap in Series
Next Topic: Difference between Object types and collection types
Goto Forum:
  


Current Time: Fri Oct 24 11:14:35 CDT 2014

Total time taken to generate the page: 0.09001 seconds