how to find second largest number through a query in sql
how to find second largest number or salary through a query in sql.
help in this i am waitng for ur reply...
If u r using SQL server 2005. The following link will help u
with this code i got the 2nd highest price
SELECT price from movies c1
WHERE 2=(SELECT count(*) from movies c2
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
Select Age From [AMST].[DBO].EmpMaster E1
Where 2=(Select Count(*) From [AMST].[DBO].Emp_Master E2
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.
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
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 tmptable (name varchar(20),age int)
Insert into tmptable Values('Hari',27)
Insert into tmptable Values('Sai gopal',27)
Insert into tmptable Values('Jeeva',29)
Insert into tmptable Values('Shanmuga Raja',25)
Insert into tmptable Values('Senthil',26)
Insert into tmptable Values('Sandesh',27)
Insert into tmptable Values('Segu',25)
Select Age From tmptable t1
Where 2=(Select Count(*) From tmptable t2
--Drop table tmptable
iam using 2000
to get 27 use 4
to get 26 use5
to get 25 use7
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
Please don't we waste this thread.
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.
keep touch with me
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
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.
I'm really sorry. Becuase ur query is working perfectly. gr8.
Here i checked throughly. It's working perfectly.
Keep touch with me
Have a nice day
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.
hi hari & sandesh,
sorry sorry , i went to my native place so i could not able to contact u ,
thank u for ur reply,
from the first reply itself i got the answer ok.
what about the second query ,1st query is working fine.
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
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.
I did something like this with basic trial tables...
To find second largest salary rows
SELECT * FROM EMP
WHERESAL IN (SELECT MAX(SAL) FROM EMP
WHERESAL <> (SELECT MAX(SAL) FROM EMP))
1 7788 SCOTT ANALYST 7566 4/19/1987 3000.00 20
2 7902 FORD ANALYST 7566 12/3/1981 3000.00 20
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.
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.
Anyway Thankyou for your toooooo late reply
Please Don't waste thread...n pls avoide duplicate post..
FROM (SELECT sal
order by sal desc)
select ename, sal
from (select ename,sal,dense_rank() over(order by sal desc) dr from emp)
where dr = &rnk
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!
I tied using the above query.
But I am getting unexpected results!!!!!
SQL query to find the second largest number(if data is sorted in
select distinct(num) from data d1 where 2=(select
count(distinct(num)) from data d2 where d1.num>d2.num);!--removed tag-->
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);!--removed tag-->
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!--removed tag-->
- arif ahmad replied to How to receive data in web ...
- William Thompson replied to What is the name of the Win...
- Sameera Piyadigamage replied to Point of Sale Developers: H...
- Scott Carline replied to 4 x C# Developers for large...
- Rajendra Dhakal replied to Restore SQL Server text dat...
- cloud rainda replied to How to convert between TS f...