Home » SQL & PL/SQL » SQL & PL/SQL » Sql Problem (merged 4)
Sql Problem (merged 4) [message #419847] Tue, 25 August 2009 14:28 Go to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

I am dealing with similar to this kind of situation.. I have table containing following data...


LOAN#      seq_id        Pymt_date
123	    1            09/20/2002
123         2            10/20/2003
123         3            10/20/2004

134         1            10/20/2004
134         2            10/20/2003
134         3            10/20/2005

138         1            10/20/2005
138         2            10/20/2004
138         3            12/12/2003

144         1            10/20/2005
144         1            10/20/2008
144         1            10/20/2009



I need to select only those loans# from the table, where min date is not sync wtih the min seq id.

For example,

like Loan# 134, where min date of Loan# 134 is not sync with Min seq_id. The Min date is 10/20/2003
and the corresponding Seq_id is 2.


Any small hint would be appreciated...

Thanks in advance..

[Updated on: Tue, 25 August 2009 14:37] by Moderator

Report message to a moderator

Re: Sql Problem [message #419851 is a reply to message #419847] Tue, 25 August 2009 14:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at MIN function in its analytic form.

Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel




Why many people think giving "sql problem" in SQL forum is wise and meaninful?

[Updated on: Tue, 25 August 2009 14:36]

Report message to a moderator

Re: Sql Problem [message #419856 is a reply to message #419847] Tue, 25 August 2009 14:50 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>By Mistaken i have post the same question twice..
& you have trouble counting, too.
This is the 4th time you posted same problem.
Perhaps remedial Browser training should taken.
Re: Sql Problem [message #419860 is a reply to message #419851] Tue, 25 August 2009 15:09 Go to previous messageGo to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

Sorry, i mentioned the same seq_id for loan# 144. It should e 1,2 and 3. and i corrected that below..

Anyway here is the sample test case of the situation...

create table test
(
loan_number number,
seq_id number,
pymt_date date
);

insert into test values (123,1,to_date('09/20/2002','MM/DD/YYYY'));
insert into test values (123,2,to_date('10/20/2003','MM/DD/YYYY'));
insert into test values (123,3,to_date('10/20/2004','MM/DD/YYYY'));
commit;
insert into test values (134,1,to_date('10/20/2004','MM/DD/YYYY'));
insert into test values (134,2,to_date('10/20/2003','MM/DD/YYYY'));
insert into test values (134,3,to_date('10/20/2005','MM/DD/YYYY'));
commit;
insert into test values (138,1,to_date('10/20/2005','MM/DD/YYYY'));
insert into test values (138,2,to_date('10/20/2004','MM/DD/YYYY'));
insert into test values (138,3,to_date('12/12/2003','MM/DD/YYYY'));
commit;
insert into test values (138,1,to_date('10/20/2005','MM/DD/YYYY'));
insert into test values (138,2,to_date('10/20/2004','MM/DD/YYYY'));
insert into test values (138,3,to_date('12/12/2003','MM/DD/YYYY'));
commit;
insert into test values (144,1,to_date('10/20/2005','MM/DD/YYYY'));
insert into test values (144,2,to_date('10/20/2008','MM/DD/YYYY'));
insert into test values (144,3,to_date('12/12/2009','MM/DD/YYYY'));
commit;


The table should show this data...


LOAN#      seq_id        Pymt_date
123	    1            09/20/2002
123         2            10/20/2003
123         3            10/20/2004

134         1            10/20/2004
134         2            10/20/2003
134         3            10/20/2005

138         1            10/20/2005
138         2            10/20/2004
138         3            12/12/2003

144         1            10/20/2005
144         2            10/20/2008
144         3            10/20/2009




And the query should show this result..


LOAN#      seq_id        Pymt_date
134         1            10/20/2004
134         2            10/20/2003
134         3            10/20/2005

138         1            10/20/2005
138         2            10/20/2004
138         3            12/12/2003



Any small hint would be appreciated...

Thanks in advance...
Re: Sql Problem (merged 3) [message #419861 is a reply to message #419847] Tue, 25 August 2009 15:18 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>Any small hint would be appreciated...

Have a look at MIN function in its analytic form.
Re: Sql Problem (merged 3) [message #419865 is a reply to message #419861] Tue, 25 August 2009 15:39 Go to previous messageGo to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

Any further hint for my dummy Brain..
Re: Sql Problem (merged 4) [message #419873 is a reply to message #419847] Tue, 25 August 2009 17:27 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
SELECT manager_id, last_name, hire_date, salary,
   MIN(salary) OVER(PARTITION BY manager_id ORDER BY hire_date
   RANGE UNBOUNDED PRECEDING) AS p_cmin
   FROM employees;

MANAGER_ID LAST_NAME                 HIRE_DATE     SALARY     P_CMIN
---------- ------------------------- --------- ---------- ----------
       100 Kochhar                   21-SEP-89      17000      17000
       100 De Haan                   13-JAN-93      17000      17000
       100 Raphaely                  07-DEC-94      11000      11000
       100 Kaufling                  01-MAY-95       7900       7900
       100 Hartstein                 17-FEB-96      13000       7900
       100 Weiss                     18-JUL-96       8000       7900
       100 Russell                   01-OCT-96      14000       7900
       100 Partners                  05-JAN-97      13500       7900
       100 Errazuriz                 10-MAR-97      12000       7900
Re: Sql Problem (merged 4) [message #419875 is a reply to message #419873] Tue, 25 August 2009 18:30 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
test thououghly for null vals, dup vals or whatever...

create table test
(
loan_number number,
seq_id number,
pymt_date date
);

insert into test values (123,1,to_date('09/20/2002','MM/DD/YYYY'));
insert into test values (123,2,to_date('10/20/2003','MM/DD/YYYY'));
insert into test values (123,3,to_date('10/20/2004','MM/DD/YYYY'));

insert into test values (134,1,to_date('10/20/2004','MM/DD/YYYY'));
insert into test values (134,2,to_date('10/20/2003','MM/DD/YYYY'));
insert into test values (134,3,to_date('10/20/2005','MM/DD/YYYY'));

insert into test values (138,1,to_date('10/20/2005','MM/DD/YYYY'));
insert into test values (138,2,to_date('10/20/2004','MM/DD/YYYY'));
insert into test values (138,3,to_date('12/12/2003','MM/DD/YYYY'));

insert into test values (144,1,to_date('10/20/2005','MM/DD/YYYY'));
insert into test values (144,2,to_date('10/20/2008','MM/DD/YYYY'));
insert into test values (144,3,to_date('12/12/2009','MM/DD/YYYY'));
commit;


select * from (
    select loan_number,seq_id,pymt_date, 
           RANK () OVER (PARTITION BY loan_number ORDER BY loan_number,pymt_date ) AS dt_rnk,
           RANK () OVER (PARTITION BY loan_number ORDER BY loan_number,seq_id ) AS seq_rnk
    from test)
where dt_rnk != seq_rnk




LOAN_NUMBER     SEQ_ID PYMT_DATE     DT_RNK    SEQ_RNK
----------- ---------- --------- ---------- ----------
        134          2 20-OCT-03          1          2
        134          1 20-OCT-04          2          1
        138          3 12-DEC-03          1          3
        138          1 20-OCT-05          3          1
Re: Sql Problem (merged 4) [message #420019 is a reply to message #419875] Wed, 26 August 2009 09:52 Go to previous message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

The Idea works well.. Thanks everybody.
Previous Topic: sql problem
Next Topic: Fixing Jobs with Time
Goto Forum:
  


Current Time: Wed Dec 07 04:44:48 CST 2016

Total time taken to generate the page: 0.07496 seconds