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

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

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

 

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)

Hi Sandesh_Segu
 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].Emp_Master 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
 

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)

Hi sandesh_segu
 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 tmp_table Values('Hari',27)
Insert into tmp_table Values('Sai gopal',27)
Insert into tmp_table Values('Jeeva',29)
Insert into tmp_table Values('Shanmuga Raja',25)
Insert into tmp_table Values('Senthil',26)
Insert into tmp_table Values('Sandesh',27)
Insert into tmp_table Values('Segu',25)

Select Age From tmp_table t1
 Where 2=(Select Count(*) From tmp_table t2
 Where t1.Age<=t2.Age)

--Drop table tmp_table


Regards
Hari K

hi hari,

iam using 2000

to get 27 use 4

to get 26 use5

to get 25 use7

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)

Hi sandesh
 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.

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

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.

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
 

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

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

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.