--**************************************
-- Name: Finding No of Max values
-- Description:To finding no of max values from a table.max function gives only one value, but this procedure gives top max no of values.I have created this procedure in sybase. U can check the procedure using same logic in other databse also
-- By: Kiran K Surve
--
-- Inputs:it takes no as input
--
-- Returns:No. of rows retrieves with top max values as per input no.
--
-- Assumes:Create a table say original table with 2 columns, 1st col is unique & second columns contain max value column.
--
-- Side Effects:no side effect
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=1338&lngWId=5--for details.--**************************************

-- the below procedure is developed in sybase 12.1
--the following procedure finds no of rows of max values as per requirement
--if one want to retrieve 25 no of maxvaluerows then procedure gets parameter 25 as input
create procedure no_maxfindinoriginal_table(@rrow int)
as
begin
declare @c int
select @c = 1
create table #temp
(col1 int,
 valuecol numeric(15,2))
create table #temp00
(col1 int,
 valuecol numeric(15,2))
insert into #temp
select Col1,valuecol
from original_table
while @c <= @rrow 
begin	
	insert into #temp00
	select Col1,valuecol 
	from #temp having valuecol = max(valuecol)
	
	delete from #temp
	where (convert(char(6),col1)+convert(char(17),valuecol)) in (select 
	(convert(char(6),col1)+convert(char(17),valuecol)) from #temp00)
	select @c = @c + 1 
end
select #temp00.col1, #temp00.valuecol from #temp00
end;