suresh

• Jun 27th, 2006

 

select max(field1) from tname1 where field1=(select max(field1) from tname1 where field1<(select max(field1) from tname1);

Field1- Salary field

Tname= Table name.

darshil

• Jun 28th, 2006

 

simpler way would be:select min(column) from (select column from table order by column desc) where rownum <= 2cheers....darshil

Santosh

• Jul 1st, 2006

 

Very Simple.........

select max(field1) from table_name where field1 < (select max(field1) from table_name)

Palak

• Jul 3rd, 2006

 

select max(column name) from employee where column name NOT IN (select max(column name) from employee)

irshad

• Jul 6th, 2006

 

SELECT MAX(Column1) FROM Tables WHERE Column1 < (SELECT MAX(Column1) FROM Table1)

pankaj kaushik

• Jul 21st, 2006

 

Select Top 1 Field1 from TableName Where Field1 <=(Select Max(Field1) from TableName) Order by Field1

shriramesh

 Profile 

• Jul 22nd, 2006

 

SELECT max(fieldname)-1 as v FROM tablename

Here : v = variablename

Guest

• Jul 22nd, 2006

 

SELECT max(fieldname) from tablename where fieldname <= select fieldname from tablename where fieldname<max(fieldname)

Sachin

• Aug 3rd, 2006

 

This is the most simplest way for finding any highest number (1,2,3,...n)

select a.* from emp a
where n=(select count(distinct b.empsal) from emp b where a.empsal<=b.empsal)

--where n =2 for the second  highest.

yakhubpasha

• Aug 12th, 2006

 

      select min(sal) from emp where sal in

        ( select top(2) from emp

         order by sal);

         

Vijay Soni

• Aug 16th, 2006

 

select max(t1.column_name) from table t1 where n-1 < (select count(distinct(t2.column_name)) from table t2 where t1.column_name > t2.column_name)

note: t1,t2 are the alias of same table this is inner join and (n-1) means put n=(the valuue u want to findout in this case put n=2)

chanti4u123

 Profile Answers by chanti4u123 

• Sep 21st, 2006

 

suppose if the sal max is 5000 and the second max sal is 3000 then

select * from emp where sal=(select max(sal) from emp where sal<(select max(sal) from emp));

sunil_ronanki

 Profile Answers by sunil_ronanki 

• Oct 16th, 2008

 

select max(columnname) from tablename where columnname in (select columnname from tablename where columnname<max(columnname));

yuvaevergreen

 Profile Answers by yuvaevergreen 

• Jun 14th, 2011

 

In Teradata, below query can be used for 2nd max even if duplicates are present.

sel b.* from 

(

sel a.*,rank() over (order by a.salary desc) as rnk from

(sel salary  from samples.emp

group by salary ) as a

) b

where b.rnk=2

yuvaevergreen

 Profile Answers by yuvaevergreen 

• Jun 14th, 2011

 

Good option from one of the answers...sel max(salary) from emp1 where salary < (sel max(salary) from emp1)

Akila

• Oct 11th, 2018

 

SELECT *
FROM
(SELECT personid ,row_number() over (order by PersonID desc) as row_num FROM
(SELECT DISTINCT personid FROM persons))
WHERE row_num = 2

Pendo

• Jul 17th, 2021

 

WITH Xlist as (
SELECT salary ,rank() over( order by salary desc ) as rnk
FROM salary)
SELECT salary from Xlist where rnk =2;