SQL Query for max number - 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

Transact-SQL (2000)

SQL Query for max number

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-24 : 10:40:10

Gopalakrishnan writes "How do I write the query for getting N-th Maximum Number in a table by passing a paramerer(N). For example if I want 6th Maximum of a field in table I will pass 6 as a parameter. This Query will have to fetch the 6th Maximum Number."

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-04-24 : 11:32:19

search for WHAT'S AFTER TOP in the FAQ's....you have a variation of the problem described there.....also some DYNAMIC SQL will be the solution to your problem.

efelito
Constraint Violating Yak Guru

478 Posts

Posted - 2002-04-24 : 11:36:50

Give this a try... I didn't have a chance to test it.

DECLARE @topn int
DECLARE @sqlstmt varchar(4000)
SET @topn = 6

SET @sqlstmt = 'SELECT TOP 1 MYVALUE
FROM (SELECT TOP ' + CAST(@topn AS varchar(10)) + ' MYVALUE FROM MYTABLE ORDER BY MYVALUE DESC) AS T1
ORDER BY MYVALUE DESC'
EXEC(@sqlstmt)

Jeff Banschbach
Consultant, MCDBA

*** Out typed again!

Edited by - efelito on 04/24/2002 11:37:42

jbkayne
Posting Yak Master

100 Posts

Posted - 2002-04-24 : 12:39:37

Why do you need dynamic sql?

Here is an example I wrote using northwind.

use northwind

declare @maxnumber int
, @employeeid int

select @maxnumber = 6

set rowcount @maxnumber

select @employeeid = employeeid
from employees
order by employeeid desc

print 'maxnumber = ' + convert(varchar,@employeeid)

vganesh76
Yak Posting Veteran

64 Posts

Posted - 2002-05-14 : 10:32:19

Declare @rowno
set @rowno = 3
select a.col1,a.col2 from table1 a
where (select count(*) + 1 from
table1 b
where b.col1 < a.col1) = @rowno

-- note include col1 is the primary key in table1
incase on composite primary keys then
give the where clause as follows

where ( select count(*) + 1 from
table1 b
where b.col1 < a.col1 and a.col2=b.col2) = @rowno
In the above table1 has col1,col2 as composite primary keys

ur feed backs
vganesh76@rediffmail.com
vganesh@mahindralogisoft.com

Enjoy working

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources