How to retrieve MAX record - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to retrieve MAX record

Author

Topic

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2013-07-09 : 07:56:26

Hi ,

I have retrieved the data using CTE . But still need the retrieve the
latest row number record from my result.



;with cte as 
(

Select ROW_NUMBER() over ( PARTITION by [T7S1_PRODUCT_CDE_original],[T7S1_BR_NO_original]  order by [sql_updated] ) rn , [sql_updated]
     
      ,[T7S1_TYP_CDE]
   
      ,[T7S1_PRODUCT_CDE_original]
 
      ,[T7S1_BR_NO_original] from [interface_i084].[dbo].[tb_i084_ds_CeresTLStageFileDocDetail](nolock)
      
)


Select * from cte 

My Query result:

rn	sql_updated	T7S1_TYP_CDE	T7S1_PRODUCT_CDE_original	T7S1_BR_NO_original
1	2012-06-26 00:17:32.007	703	A0200030OOO    	00066
2	2012-06-27 12:30:21.803	703	A0200030OOO    	00066
3	2012-07-13 01:15:36.073	703	A0200030OOO    	00066
1	2012-06-27 12:45:30.653	703	A0200030OOO    	00083
1	2012-06-25 07:45:33.907	703	A0200030OOO    	00090
2	2012-08-16 12:45:22.227	703	A0200030OOO    	00090

Now Expecting:


rn	sql_updated	T7S1_TYP_CDE	T7S1_PRODUCT_CDE_original	T7S1_BR_NO_original
3	2012-07-13 01:15:36.073	703	A0200030OOO    	00066
1	2012-06-27 12:45:30.653	703	A0200030OOO    	00083
2	2012-08-16 12:45:22.227	703	A0200030OOO    	00090


I want to retrieve the MAX rn  record.. Please suggest

Vijay is here to learn something from you guys.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-09 : 08:10:25

Add a where clause to your final select to pick only RN=1. Also, if you want the latest record, change the order by clause to descending.

;with cte as 
(

Select ROW_NUMBER() over ( PARTITION by [T7S1_PRODUCT_CDE_original],[T7S1_BR_NO_original]  order by [sql_updated] DESC ) rn , [sql_updated]
     
      ,[T7S1_TYP_CDE]
   
      ,[T7S1_PRODUCT_CDE_original]
 
      ,[T7S1_BR_NO_original] from [interface_i084].[dbo].[tb_i084_ds_CeresTLStageFileDocDetail](nolock)
      
)


Select * from cte WHERE rn=1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources