CODEWITHSUNDEEP
sql
user2865419
user2865419

Reputation: 17

Sql query - Oracle database

empid    emplrcd   effdt   effsq
101          #1    2/1/99    0
101          #1    3/1/13    1
101          #1    23/3/13   1 
101          #1    22/6/13   2
102          #2    20/6/91   1

I need to retrieve row 4, and I have written a partial code, please help me with the other half.

select a* 
from Ps_Job a 
where a.empid = '101' 
and a.emprcd ='#1' 
and a.effdt = (select max(a1.effdt) from Psjob1) where...............
and a.effseq = (Select  max(a2.effseq) from Ps_job2)
where..............

Please help me with the where caluse which should be generic and not row specific. i think it should be filled with nth max concept but not sure.

Upvotes: 0

Views: 87

Answers (1)

SSP
SSP

Reputation: 2670

In oracle 

select *
from 
(select a*  from Ps_Job a 
  where a.empid = '101' 
  and a.emprcd ='#1' 
  and a.effdt = (select max(a1.effdt) from Psjob1)  where ... 
  and a.effseq = (Select  max(a2.effseq) from Ps_job2)
  where ..... ) 
where ROWNUM == **The line number what you want to get**;

In sql

SELECT * from Ps_Job LIMIT 3,1where(

     select a*  from Ps_Job a 
      where a.empid = '101' 
      and a.emprcd ='#1' 
      and a.effdt = (select max(a1.effdt) from Psjob1)  where ... 
      and a.effseq = (Select  max(a2.effseq) from Ps_job2)
      where .....  )

Upvotes: 1

Related Questions