Sql query - Oracle database

Question

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.

• Andy Lester: Try writing something yourself and then if it doesn't work, show us specifically what you did so we can help you along. You start it, we help. We don't write it for you. Show us the actual code that you've tried and then we can help you from there. Chances are you'll get pretty close to the answer if you just try it yourself first.
• user2865419: Thanks Andy. I have tried in where clause -
• user2865419: where (n-1) = (select count (distinct(a1.effseq) from psjob1 where a2.effseq > a1.effseq ), but the code doesn't logically fit.
• Vishwanath Dalvi: @user2865419 on what conditions you want to get 4th row? based on max date and sq?
• SSP: is ur requirement to get last row of same specific ID ?

Answer 1

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 ..... )

• user330315: I wonder how a statement that won't work on Oracle can be accepted as an answer to a question that says "Oracle" in the subject
• SSP: it's taged as a sql so i put this one as answer
• user330315: sql is not a specific DBMS product and relates to ANSI SQL (which doesn't have LIMIT with that syntax). But my suprise is more related to the fact that user2865419 accepted the answer altough it clearly could not have worked for him.