About row_number()

Transact-SQL
1

Hi All,

I just take over a new project and there is a sql statement in a schedule job which I really don't understand.
I would be very appreciated if some expert can explain it for me.

Database : SQL Server 2016
(Some unnecessary field was removed for easy to read)

1 select top 50 sp_key, stat_txt, count, status, lst_mod_dt, category, item_type
2 from (
3 select cast(s.sp_key as varchar(255)) as sp_key,
4 cast(s.stat_txt as varchar(255)) as stat_txt, 1 as count,
5 'Created' as status, s.lst_mod_dt,
6 cast(p.CATG_TXT as varchar(255)) as category,
7 row_number() over (partition by s.sp_key order by s.sp_key desc) as rownum,
8 row_number() over (order by s.sp_key) as row_count,
9 cast(p.itype_txt as varchar(255)) as item_type
10 from TableA s
11 inner hash join TableB p on s.PRD_KEY = p.PRD_KEY
12 left hash join TableC lc on lc.sample_request_key = s.sp_key
13 left hash join (select * from TableA where stat_txt not in ('Created')) st on st.sp_key = s.sp_key
14 where s.CRT_DT >'2015-02-15' and lc.sample_request_key is null and st.stat_txt is not null
15 ) as result where result.rownum = 1

	select top 50  sp_key, stat_txt, count, status, lst_mod_dt, category, item_type 
	from ( 				
		select cast(s.sp_key as varchar(255)) as sp_key,  
			cast(s.stat_txt as varchar(255)) as stat_txt, 1 as count, 
			'Created' as status, s.lst_mod_dt, 		
			cast(p.CATG_TXT as varchar(255)) as category, 	 
			row_number() over (partition by s.sp_key order by s.sp_key desc) as rownum,
			row_number() over (order by s.sp_key) as row_count, 	
			cast(p.itype_txt as varchar(255)) as item_type  
		from TableA s 
			inner hash join TableB p on s.PRD_KEY = p.PRD_KEY 
			left hash join TableC lc on lc.sample_request_key = s.sp_key 
			left hash join (select * from TableA where stat_txt not in ('Created')) st on st.sp_key = s.sp_key
		where s.CRT_DT >'2015-02-15' and lc.sample_request_key is null and st.stat_txt is not null
	) as result where result.rownum = 1

what I don't understand is line 13 and line 15

for line 13, why it have a left join on the same table and not simple place "stat_txt not in ('Created')" on the where clause on line 14?

for line 15, what is the purpose "as result where result.rownum = 1"?

Thanks

2

I have test that line 13 is useless

I removed line 13 and change line 14 as

where s.CRT_DT >'2015-02-15' and lc.sample_request_key is null and s.stat_txt is not null and s.stat_txt not in ('Created')

I got the same number of row, same result

Is there any reason to use left join rather than simple add to the where clause?

3

for line 15 issue,

I simple run the subquery and found that rownum is always return 1, than why it need a where clause (line 15) and it seem the subquery is also not necessary, am i right?

		select cast(s.sp_key as varchar(255)) as sp_key,  
			cast(s.stat_txt as varchar(255)) as stat_txt, 1 as count, 
			'Created' as status, s.lst_mod_dt, 		
			cast(p.CATG_TXT as varchar(255)) as category, 	 
			row_number() over (partition by s.sp_key order by s.sp_key desc) as rownum,
			row_number() over (order by s.sp_key) as row_count, 	
			cast(p.itype_txt as varchar(255)) as item_type  
		from TableA s 
			inner hash join TableB p on s.PRD_KEY = p.PRD_KEY 
			left hash join TableC lc on lc.sample_request_key = s.sp_key 13				
		where s.CRT_DT >'2015-02-15' and lc.sample_request_key is null and s.stat_txt is not null and s.stat_txt not in ('Created')
4

12 left hash join TableC lc on lc.sample_request_key = s.sp_key
13 left hash join (select * from TableA where stat_txt not in ('Created')) st on st.sp_key = s.sp_key
14 where s.CRT_DT >'2015-02-15' and lc.sample_request_key is null and st.stat_txt is not nul

I found another weird statement on line 12 and line 14 as the "lc.sample_request_key is null", however on line 12 the left join is on lc.sample_request_key = s.sp_key

it seem "lc.sample_request_key is null" is also useless...