How do we display last record of each employee using row_number

Transact-SQL
1

create table #table
([EMP_CODE] int ,[YYYYMM] varchar(10),[EMP_FNAME] varchar(100),[BASIC] numeric(10,2) ,[HRA] numeric(10,2) ,[DA] numeric(10,2) )

insert into #table ([EMP_CODE] ,[YYYYMM],[EMP_FNAME],[BASIC] ,[HRA] ,[DA] )
values(101,'201401','tisara',1000,500,250)
insert into #table ([EMP_CODE] ,[YYYYMM],[EMP_FNAME],[BASIC] ,[HRA] ,[DA] )
values(101,'201402','tisara',1000,500,250)
insert into #table ([EMP_CODE] ,[YYYYMM],[EMP_FNAME],[BASIC] ,[HRA] ,[DA] )
values(101,'201403','tisara',1000,500,250)
insert into #table ([EMP_CODE] ,[YYYYMM],[EMP_FNAME],[BASIC] ,[HRA] ,[DA] )
values(101,'201404','tisara',1000,500,250)
insert into #table ([EMP_CODE] ,[YYYYMM],[EMP_FNAME],[BASIC] ,[HRA] ,[DA] )
values(102,'201501','Lewis',2000,500,150)
insert into #table ([EMP_CODE] ,[YYYYMM],[EMP_FNAME],[BASIC] ,[HRA] ,[DA] )
values(102,'201502','Lewis',2000,500,150)
insert into #table ([EMP_CODE] ,[YYYYMM],[EMP_FNAME],[BASIC] ,[HRA] ,[DA] )
values(102,'201503','Lewis',2000,500,150)
insert into #table ([EMP_CODE] ,[YYYYMM],[EMP_FNAME],[BASIC] ,[HRA] ,[DA] )
values(103,'201302','Chris',5000,100,50)
insert into #table ([EMP_CODE] ,[YYYYMM],[EMP_FNAME],[BASIC] ,[HRA] ,[DA] )
values(103,'201303','Chris',5000,100,50)

SELECT row_number() over(partition by EMP_CODE order by EMP_CODE) as rowid
,[EMP_CODE] ,[YYYYMM] ,[EMP_FNAME] ,[BASIC] ,[HRA] ,[DA]
FROM #table

drop table #table

2

the way you are getting the row_number is random. You are ordering by Emp_code. Do you now care about YYYYMM? Is YYYYMM and Emp_Code unique?? It looks like you want the last YYYYMM by emp_code, but not clear.

If you want max YYYYMM by Emp_code then this will work

SELECT e.[EMP_CODE] ,e.[YYYYMM] ,e.[EMP_FNAME] ,e.[BASIC] ,e.[HRA] ,e.[DA]
  FROM #Table e
	join (select emp_code, max(yyyymm) as YYYYMM
		    from #Table
		group by emp_code) m
	 on e.emp_code = m.Emp_code
	 and e.yyyymm = m.yyyymm
2 Likes
3 
SELECT [EMP_CODE] ,[YYYYMM] ,[EMP_FNAME] ,[BASIC] ,[HRA] ,[DA]
FROM (
    SELECT *, row_number() over(partition by EMP_CODE order by [YYYYMM] DESC) as rowid    
    FROM #table
) AS derived
WHERE rowid = 1
2 Likes
4

Thank you both ScottPletcher & mike01 for your time.

5

how about

top 1 order by column desc

just top of my head
Obviously it could be bad or something missing

2 Likes
6

Not sure what you are referring to...but an alternative using TOP 1 would be:

SELECT TOP 1 WITH TIES
       [EMP_CODE]
     , [YYYYMM]
     , [EMP_FNAME]
     , [BASIC]
     , [HRA]
     , [DA]
  FROM #table
 ORDER BY
       row_number() over(partition by EMP_CODE order by [YYYYMM] DESC);
2 Likes
7

Jeff

Obviously it could be bad or something missing

what i mean is !!

it could be bad = performance wise , or accuracy wise ( results order by may not be reliable ) this may not be the best approach

something missing = 
1) it was on the top of my head .. i did not do any testing ... it could be totally wrong in the sense that .. 
2) from top of my head to actual implementation with accuracy
    takes a few steps !! 
    checking my thinking ..testing .. etc etc

hope this helps
:slight_smile: