# how to find second largest number through a query in sql

db , sql
• 11 years ago

how to find second largest number or salary through a query in sql.

help in this i am waitng for ur reply...

• 11 years ago

Hi Altbert
If u r using SQL server 2005. The following link will help u

http://www.4guysfromrolla.com/webtech/010406-1.shtml

Good Luck

Regards
Hari K

• 11 years ago

hi,

with this code i got the 2nd highest price

SELECT  price from movies c1
WHERE 2=(SELECT count(*) from movies c2
WHERE C1.price<=C2.price)

• 11 years ago

Hi SandeshSegu
Ur logic is absoultely wrong. What it's doing u know.

I checked ur coding. I got the wrong answer.

checked ex: In my Age Field has the record 25,25,27,29,26

Query

Select Age From [AMS
T].[DBO].EmpMaster E1
Where 2=(Select Count(*) From [AMS
T].[DBO].Emp_Master E2
Where E1.Age<=E2.Age)

I am getting 27 Actuall answer is 26. How can i achieve this?

so i think this logic is not suitable for getting 2nd or 3rd, etc position.

Actually i done this concept using procedure 2 years ago.
In oracle we can achieve using Rownum keyword in the query. Now in SQL server 2005 has this RowNum concept.

i hope u can understand what is problem in ur query.

Regards
Hari K

• 11 years ago

hi hari ,iam getting the correct values with that query.

if u want 26 then try this query

Select Age From age E1
Where 3=(Select Count(*) From age E2
Where E1.Age<=E2.Age)

• 11 years ago

Hi sandeshsegu
R u using SQL Server 2000 or 2005?
Becuase i didn't get any value when i execute this query with where 3 condition instead of using 2.

If u  r using SQL Server 2000 u can check it with any other temp table.

you just copy and paste the follwoing script into ur query analyzer. And test it. Tell me What is the answer r u getting?

Create table tmp
table (name varchar(20),age int)

Insert into tmptable Values('Hari',27)
Insert into tmp
table Values('Sai gopal',27)
Insert into tmptable Values('Jeeva',29)
Insert into tmp
table Values('Shanmuga Raja',25)
Insert into tmptable Values('Senthil',26)
Insert into tmp
table Values('Sandesh',27)
Insert into tmptable Values('Segu',25)

Select Age From tmp
table t1
Where 2=(Select Count(*) From tmptable t2
Where t1.Age<=t2.Age)

--Drop table tmp
table

Regards
Hari K

• 11 years ago

hi hari,

iam using 2000

to get 27 use 4

to get 26 use5

to get 25 use7

• 11 years ago

hi hari,

ok,try this to find 2nd max/highest

select * from tmp_table m1
where (2-1)=(select count(distinct(m2.age))
from tmp_table m2 where
m2.age>m1.age)

• 11 years ago

Hi sandesh
The previous query also absolutly wrong concept. its also not working perfect.

In my knowledge we can achieve through SP. The following link may guide u for how to achieve nth position record.

http://database.ittoolbox.com/documents/popular-q-and-a/how-to-find-the-nth-maxmin-records-in-sql-server-2803

keep touch with me

Good Luck

Regards
Hari K

• 11 years ago

hi hari,

this query is workimg very fine with me & i dont know what happened in ur case.

select * from tmp_table m1
where (n-1)=(select count(distinct(m2.age))
from tmp_table m2 where
m2.age>m1.age)

replace n by which ever number u want

for example replace n by 2 toget the 2nd max

& replace n by 3 toget the 3rd max & so on.

i tried with different tables & iam getting correct values.

ok bye keep in touch with me.

• 11 years ago

Hi Sandesh_segu

I'm really sorry. Becuase ur query is working perfectly. gr8.

Here i checked throughly. It's working perfectly.

Keep touch with me

Take care

Have a nice day

Warm Regards
Hari K

• 11 years ago

Hi Sandesh
You see here we r discussed about how to get the n'th  max record. But actully this query is started from Mr.Albert Ponuraj. I don't know where he went? Becuase we didn't get any feedback from him for is he solved or not.

Anyway Sandesh i got new logic from u.

Thakyou indeed.

Regards
Hari K

• 11 years ago

hi hari & sandesh,

sorry sorry , i went to my native place so i could not able to contact u ,

what about the second query ,1st query is working fine.

• 11 years ago

hi albert ,

u can use the 2nd query for finding nth max/highest by replacing n by whichever

number or highest u want.

for example if u want 3rd highest replace n by 3 and u will get the 3rd highest

• 10 years ago

Hello sir,

i created one application for the medical domain,in that i am using vb/sql.

while saving the records i am getting one error called "no transaction active" or " can't create new transaction because capacity was exceeded"

help me to solve this problem yar.

• 10 years ago

I did something like this with basic trial tables...

To find second largest salary rows

SELECT * FROM EMP

WHERE

SAL IN (SELECT MAX(SAL) FROM EMP

WHERE

SAL <> (SELECT MAX(SAL) FROM EMP))

result:

1 7788 SCOTT ANALYST 7566 4/19/1987 3000.00  20
2 7902 FORD ANALYST 7566 12/3/1981 3000.00  20

krish

• 10 years ago

Hi

I'm a new member and I dnt know where to post my problem in VB so I decide to write in you. Can you please help me with this problem... I have a dbgrid1 in my project, the datasource is on the DATA1 where the DATA1 is my object that connects to the mdb file (MS Access). i.e. there are 3 fielname namely name, age, sex. I want the field NAME and AGE will be disabled and cannot be written by the user except the AGE. I want to edit on the AGE field but as what I said the user should cannot write on the tweo fields the name and age.

Hope you can help me on this problem.

thank you very much.

jireh

• 9 years ago

Hi Preamkumar,

Your query is applicable for Oracle, Its wont work in SQL server.  I am implemented this rownum concept  in Oracle 5yrs back itself.

In SQL server 2005, MS provide for rownum concept.

Note:

Regards

Hari K.......

• 9 years ago

SELECT min(sal)
FROM (SELECT sal
FROM emp
order by sal desc)
WHERE rownum<(&nth_heighest+1);
//

select ename, sal
from (select ename,sal,dense_rank() over(order by sal desc) dr from emp)
where dr = &rnk
//

• 9 years ago

Hi Sandesh,

I am extreamely sorry...

Your query is working perfectly...Actually in that time my SQL installatllation is the problem...thats y i may got the error.

Keep in touch!

Regards

Hari K......

• 9 years ago

I tied using the above query.

But I am getting unexpected results!!!!!

• 8 years ago

SQL query to find the second largest number(if data is sorted in

ascending order):

select distinct(num) from data d1 where 2=(select

count(distinct(num)) from data d2 where d1.num>d2.num);

• 8 years ago

I am extremely sorry...... The above code is for finding the second smallest number in the data table..... Table structure is as below:

create table data( num number);

• 7 years ago

SQL query to find the second largest number

select TOP 1 fid FROM (SELECT TOP 2 fid FROM dbo.user_master ORDER BY fid DESC) AS a ORDER BY a.fid ASC

### Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

### Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“We better hurry up and start coding, there are going to be a lot of bugs to fix.”