ROWNUM in SQL

Transact-SQL
1

HI Gents,

I have below part of my query which is failing for ROWNUM (oracle) what the best way to replace this ROWNUM which is basically ROWNUMBER =1 with the SQL row_number() =1?

ISNULL((SELECT 'Y'
		 FROM    EMPLOYMENT EMPX
				,JOB JOBX
		 WHERE   EMPX.SUPERVISOR_ID = EMPID
		   AND   JOBX.EMPID  = EMPX.EMPID
		   AND   JOBX.EFFDT =
				(SELECT MAX(JOBY.FDT)
				 FROM   PS_JOB JOBY
				 WHERE  JOBY.EMPID = JOBX.EMPID
				   AND  JOBY.EMP_RCD_NBR  = JOBX.EMP_RCD_NBR
				   AND  JOBY.FDT  <=GETDATE())
		   AND   JOBX.SEQ =
				(SELECT MAX(JOBZ.SEQ)
				 FROM   JOB JOBZ
				 WHERE  JOBZ.EMPID  = JOBX.EMPID	
				   AND  JOBZ.EMP_RCD_NBR  = JOBX.EMP_RCD_NBR
				   AND  JOBZ.FDT = JOBX.FDT)
		   AND   JOBX.EMP_STATUS IN ('A', 'L', 'S')
	   AND   ROWNUM = 1),'N'
  ) AS MANAGER_FLAG,

Thanks!

2

In this case, I think:

ISNULL((SELECT TOP(1) 'Y'
		 FROM    EMPLOYMENT EMPX
				,JOB JOBX
		 WHERE   EMPX.SUPERVISOR_ID = EMPID
		   AND   JOBX.EMPID  = EMPX.EMPID
		   AND   JOBX.EFFDT =
				(SELECT MAX(JOBY.FDT)
				 FROM   PS_JOB JOBY
				 WHERE  JOBY.EMPID = JOBX.EMPID
				   AND  JOBY.EMP_RCD_NBR  = JOBX.EMP_RCD_NBR
				   AND  JOBY.FDT  <=GETDATE())
		   AND   JOBX.SEQ =
				(SELECT MAX(JOBZ.SEQ)
				 FROM   JOB JOBZ
				 WHERE  JOBZ.EMPID  = JOBX.EMPID	
				   AND  JOBZ.EMP_RCD_NBR  = JOBX.EMP_RCD_NBR
				   AND  JOBZ.FDT = JOBX.FDT)
		   AND   JOBX.EMP_STATUS IN ('A', 'L', 'S')
	   ),'N'
  ) AS MANAGER_FLAG,
3

Oh! Bitsmed, that's the only thing I did not try! looool.:joy:. I was trying row_number() over(order by ) etc..
Thanks a bunch!!!
Pasi.