Home » SQL & PL/SQL » SQL & PL/SQL » To solve..
To solve.. [message #242992] Tue, 05 June 2007 12:19 Go to next message
t.guru
Messages: 5
Registered: May 2007
Junior Member
pls help me to solve this..

i want the latest date ,crlim & acct for an account as shown in output.


Eg.
acct crlim date
100 50000 10-05-07
100 30000 20-05-07
103 20000 21-05-07
103 22000 22-05-07

output to be as follows:-
acct crlim date
100 30000 20-05-07
103 22000 22-05-07
Re: To solve.. [message #242994 is a reply to message #242992] Tue, 05 June 2007 12:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Combine the anwsers you got on your questions Nth record and Index about rank and dense_rank functions and you have your answer.

Regards
Michel
Re: To solve.. [message #243124 is a reply to message #242992] Wed, 06 June 2007 03:02 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Hi Michel,

i have tried to get same result over employee table. i wrote a query, could you please modify or suggest me some better way to do the same.

SELECT 
  y.job_id,
  y.hire_date
FROM 
  (select job_id,
  max(rid) rmax from (select job_id,
  hire_date,
  row_number() over(partition by job_id order by hire_date) rid from employee)  group by job_id) x,
  (select job_id,
  hire_date,
  row_number() over(partition by job_id order by hire_date) rid from employee)y
WHERE 
  y.job_id=x.job_id and
  x.rmax=y.rid



--Yash
Re: To solve.. [message #243128 is a reply to message #243124] Wed, 06 June 2007 03:15 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You are looking for the maximum date (latest) for a given account: that would be an aggregate function, not analytics.

WITH yourtable AS
  ( 
    SELECT 100 acct, 50000 crlim, TO_DATE('10-05-2007', 'dd-mm-yyyy') mydate FROM dual UNION ALL
    SELECT 100 acct, 30000 crlim, TO_DATE('20-05-2007', 'dd-mm-yyyy') mydate FROM dual UNION ALL
    SELECT 103 acct, 20000 crlim, TO_DATE('21-05-2007', 'dd-mm-yyyy') mydate FROM dual UNION ALL
    SELECT 103 acct, 22000 crlim, TO_DATE('22-05-2007', 'dd-mm-yyyy') mydate FROM dual 
  )
SELECT *
FROM   yourtable my
WHERE  ( my.acct, my.mydate ) IN ( SELECT acct, MAX(mydate)
                                   FROM   yourtable
                                   GROUP BY acct
                                 )
/


MHE
Re: To solve.. [message #243129 is a reply to message #243128] Wed, 06 June 2007 03:22 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Thx Maaher !! No doubt you are master in oracle
Re: To solve.. [message #243131 is a reply to message #243124] Wed, 06 June 2007 03:52 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you order by "hire_date desc" instead of "hire_date", the rows you're searching for are with rid=1 no need to search a max.

Regards
Michel
Previous Topic: CASE conditional statement
Next Topic: Function using execute immediate
Goto Forum:
  


Current Time: Sat Dec 10 05:05:51 CST 2016

Total time taken to generate the page: 0.05833 seconds