how to find second maximum frm a table

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

General SQL Server Forums

New to SQL Server Programming

how to find second maximum frm a table

Author

Topic

divyagr
Starting Member

29 Posts

Posted - 2009-01-20 : 00:00:57

hi all,

how we can find the second maximum from a table

with regards,
Divya

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-20 : 00:03:53

select * from
(select *,rank() over(partition by colname order by col1name) as seq from urtable) a where a.seq =2

Jai Krishna

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-20 : 00:04:18

can u post ur table structure?

If u want the second maximum from a table, just use row_number(), rank() functions

khushi
Starting Member

5 Posts

Posted - 2009-01-20 : 00:13:30

try this one solution:

Select top 1 * from (select top 2 * from Table_Name order by Column_Name desc)A order by Column_Name Asc

Column_name is name of column whose you have to find maximum
Table_name is the neame of that table

divyagr
Starting Member

29 Posts

Posted - 2009-01-20 : 00:18:54

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-01-20 : 00:21:12

There r lots of methods if u using sql 2005 then u use jus row_number or rank() function..
like this...

with ashishashish as
(Select *,row_number() over(partition by yourfield order by yourfield) as rn from testtable)
select * from ashishashish where rn=2

and if u using sql 2000 then

SELECT TOP 1 * FROM
(SELECT TOP (N-1) * FROM Table
ORDER BY yourOrderField DESC)t
ORDER BY yourOrderField

or like this...

SELECT *
FROM Employee E1
WHERE (N-1) = (
SELECT COUNT(DISTINCT(E2.Salary))
FROM Employee E2
WHERE E2.Salary > E1.Salary)

Thanks may that solve your problem,,,,,

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-20 : 00:25:52

declare @tab table (ID int, ODate datetime)
insert into @tab select
1 ,' 1/30/2008 9:43:34 AM' union all select
2 ,' 2/12/2008 10:07:00 AM' union all select
3 ,' 3/19/2008 3:38:00 PM' union all select
2 ,' 4/2/2008 3:52:49 PM' union all select
1 ,' 5/28/2008 5:10:14 PM' union all select
3 ,' 6/5/2008 9:28:35 AM'

select a.id,a.odate from
(select row_number() over( order by ID) as seq ,* from (select id,max(odate) as odate from @tab group by id)b) a where a.seq =2

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-01-20 : 01:32:27

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/find-nth-maximum-value.aspx

Madhivanan

Failing to plan is Planning to fail

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-01-20 : 01:42:25

select * FROM
(
select DENSE_RANK() over (order by age desc) as ordercol, * from Employee
) temp where ordercol = 2

Rahul Shinde

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources