"Second" Max function - 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

SQL Server 2000 Forums

SQL Server Development (2000)

"Second" Max function

Author

Topic

acdacd
Yak Posting Veteran

63 Posts

Posted - 2005-08-14 : 11:43:18

Sqlserver provide the Max function for us.

Then I think that i could design a UDF to select the second max(eg 4 in (4,7,3,1))

The function must "scan" all row of that table, then how to do it?

Remark: I know this can be solved by sql only, but want to know the way to construct the count(*) and Max function !!

Thx!

be a hardworking people!!

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-08-14 : 13:24:49

Do You mean something like this:

SELECT MAX(col) AS SecondMax
FROM tbl
WHERE col < (SELECT MAX(col) FROM tbl)

?

rockmoose

acdacd
Yak Posting Veteran

63 Posts

Posted - 2005-08-14 : 13:57:53

Thanks for your answer!

Actually , I know this query, but I wonder whether I can design a UDF to do the "same" job as count or max function.Therefore, i can modify it by myself in different situation, eg third max, etc

be a hardworking people!!

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-08-14 : 16:22:43

This can be utilized to find n'th maximum value for a column in a table;

SELECT MAX(<col>)
FROM <tbl> t1
WHERE
	3	--<----------- 3rd MAX value
	=
	(SELECT COUNT(*) FROM <tbl> t2 WHERE t1.<col> <= t2.<col>)

Were You after a udf that works dynamically for any column in any table ?
Don't see how You could do that.

rockmoose

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-16 : 02:15:48

and one more method

Select min(col) from (Select top 3 col from yourtable order by col desc) T

Replace 3 by the number you want

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources