Any replacement of this query.
go
Declare @MaxCount int=1--(chnage param from 1 to 11)
/*
(I need to return the average records based on the Input param @MaxCount )
*/
Declare @DistCount int
Declare @Diff int
declare @dept table (id int,name varchar(10),pincode int)
declare @temp table (sno int,id int,name varchar(10),pincode int)
insert @dept select 1,'IT',1
insert @dept select 1,'IT',1
insert @dept select 1,'IT',1
insert @dept select 2,'ITES',1
insert @dept select 2,'ITES',1
insert @dept select 2,'ITES',1
insert @dept select 3,'HR',1
insert @dept select 3,'HR',1
insert @dept select 3,'HR',1
insert @dept select 4,'ADMIN',1
insert @temp
select Row_number()over(Partition by id,name order by id desc)SNo ,* from @dept
set @DistCount =(select COUNT(distinct name) from @dept)
set @Diff=@MaxCount/@DistCount
if( @Diff<=0)
set @Diff=1
if(@MaxCount>=@DistCount)
Begin
select * from @temp
Where SNo<=@Diff
End
else
Begin
select * from (select *,Row_number() over(
order by len(name) desc )RNO from @temp
Where SNO=1) A Where RNO<=@MaxCount
End