Problem with null

[habneh]

I am trying to get the max employee change number

for some reason when an employee doesn't have change number i.e when it is null, my query doesn't pick that specific record. below is the query I wrote
what is the problem with my query

create table #tmp (ID int, EMP_ID int, EMP_VER_NUMBER int,EMP_CHANGE_NUMBER int)

insert #tmp select 1,1,1,1
insert #tmp select 2,1,1,2
insert #tmp select 3,1,1,3
insert #tmp select 4,1,1,4

insert #tmp select 5,2,1,5
insert #tmp select 6,2,1,1
insert #tmp select 7,2,1,1

insert #tmp select 8,3,2,2
insert #tmp select 9,3,2,3
insert #tmp select 10,3,2,4
insert #tmp select 11,3,3,1
insert #tmp select 12,3,3,2
insert #tmp select 13,3,3,3

insert #tmp select 14,4,3,NULL
insert #tmp select 15,4,3,2

insert #tmp select 16,5,3,NULL

insert #tmp select 17,6,3,1


SET ANSI_NULLS OFF

SELECT * FROM #tmp A
WHERE A.EMP_CHANGE_NUMBER = (SELECT MAX(EMP_CHANGE_NUMBER)
FROM #tmp B
WHERE B.EMP_ID = A.EMP_ID
GROUP BY EMP_ID)


SET ANSI_NULLS ON


drop table #tmp

the result set should look like

id emp_id emp_ver_number emp_change_number
4 1 1 4
7 2 1 5
10 3 2 4
15 4 3 2
16 5 3 NULL
17 6 3 1

but my query is missind the record with ID 16

thanks

 

[PWise]

change to

SELECT * FROM #tmp A
WHERE A.EMP_CHANGE_NUMBER = (SELECT MAX(isnull(EMP_CHANGE_NUMBER,0)) 
                             FROM #tmp B 
                             WHERE B.EMP_ID = A.EMP_ID 
                             GROUP BY EMP_ID)

 

[habneh]

Thanks

but still missing the record with ID 16 (it didn't solve my problem)

 

[Chopstik]

You problem is that NULL <> NULL. You have to convert both NULL values to zero in order to find a match. Try this:

create table #tmp (ID int, EMP_ID int, EMP_VER_NUMBER int,EMP_CHANGE_NUMBER int)

insert #tmp select 1,1,1,1
insert #tmp select 2,1,1,2
insert #tmp select 3,1,1,3
insert #tmp select 4,1,1,4

insert #tmp select 5,2,1,5
insert #tmp select 6,2,1,1
insert #tmp select 7,2,1,1

insert #tmp select 8,3,2,2
insert #tmp select 9,3,2,3
insert #tmp select 10,3,2,4
insert #tmp select 11,3,3,1
insert #tmp select 12,3,3,2
insert #tmp select 13,3,3,3
 
insert #tmp select 14,4,3,NULL
insert #tmp select 15,4,3,2

insert #tmp select 16,5,3,NULL

insert #tmp select 17,6,3,1

 
SET ANSI_NULLS OFF
 
SELECT * FROM #tmp A
WHERE [COLOR=red]ISNULL(A.EMP_CHANGE_NUMBER,0)[/color] = (SELECT MAX(ISNULL(EMP_CHANGE_NUMBER,0))
                             FROM #tmp B 
                             WHERE B.EMP_ID = A.EMP_ID 
                             GROUP BY EMP_ID)


 SET ANSI_NULLS ON 


drop table #tmp

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[habneh]

That was really cool!!!

Thank you very much