Click here to Skip to main content
15,867,834 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
<pre>
with Notesdate as
(
select KeyID,InsertedDate,NOTEID,ROW_NUMBER() over(Partition by KEYID order by InsertedDate desc) as ROWNUMBER from Note
)
--select inserteddate,KEYID from Notesdate ND where rownumber=1

SELECT Distinct		
	 
	
	          CASE WHEN LV.TypeName LIKE '%NOTES%'
	               THEN  'Notes' END AS Name,


            CASE WHEN LV.TypeName LIKE '%NOTES%'
			THEN 
			  
			  --     THEN N.InsertedDate= (Select  inserteddate from Notesdate where ROWNUMBER=1 ) 

FROM dbo.NOT N 
Inner JOiN Notesdate ND On ND.NoteID=N.NoteID

WHERE 
 N.InsertedByUserID<>'0'   



In the second case statment, I want to get the value of Inserteddate for rownumber=1.

How to achieve this?

What I have tried:

<pre>
with Notesdate as
(
select KeyID,InsertedDate,NOTEID,ROW_NUMBER() over(Partition by KEYID order by InsertedDate desc) as ROWNUMBER from Note
)
--select inserteddate,KEYID from Notesdate ND where rownumber=1

SELECT Distinct		
	 
	
	          CASE WHEN LV.TypeName LIKE '%NOTES%'
	               THEN  'Notes' END AS Name,


            CASE WHEN LV.TypeName LIKE '%NOTES%'
			THEN 
			  
			  --     THEN N.InsertedDate= (Select  inserteddate from Notesdate where ROWNUMBER=1 ) 

FROM dbo.NOT N 
Inner JOiN Notesdate ND On ND.NoteID=N.NoteID

WHERE 
 N.InsertedByUserID<>'0'   
Posted
Updated 21-May-19 23:05pm
Comments
Santosh kumar Pithani 20-May-19 3:16am    
Use your case condition should be "
CASE WHEN LV.TypeName LIKE '%NOTES% AND ND.Row_number=1
THEN
ND.inserteddate
ELSE
null END
Member 12965461 20-May-19 5:18am    
I do not want null to be printed in the result, I want result only with ND.Row_number=1
Santosh kumar Pithani 20-May-19 5:45am    
If case condition true then it returns InsertedDate , its fine but if condition fails what record should return? Default it returns null values for your understand i given null.
Member 12965461 20-May-19 7:52am    
correct. But I have to print only ND.Rownumber=1 in the result. can we do it in some different way rather than using case statement.
Member 12965461 20-May-19 5:54am    
correct. But I have to print only ND.Rownumber=1 in the result. can we do it in some different way rather than using case statement.

1 solution

 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900