Login    Forum    Search    FAQ   Blog

Board index » Server Side Development » SQL




 Page 1 of 1 [ 6 posts ] 



Author Message
 Post subject: Get 2ND Highest Among all
 Post Posted: Sat Nov 21, 2009 1:58 pm 
Offline

Joined: Sat Feb 21, 2009 11:41 am
Posts: 45
hey i need the second highest number or u could say field 4m ma
tab n all da vals of da row,,,correspondin 2 dat 2ns
highest value
Thanks iN advance....


Top 
 Post subject: Re: Get 2ND Highest Among all
 Post Posted: Sat Nov 21, 2009 1:59 pm 
Offline

Joined: Sat Feb 21, 2009 11:06 am
Posts: 1794
Code:
SELECT *
FROM YourTable
WHERE field IN
(SELECT TOP 1 field
FROM (SELECT TOP 2 field
      FROM YourTable
      ORDER BY field DESC)t
ORDER BY t.field)


Top 
 Post subject: Re: Get 2ND Highest Among all
 Post Posted: Sat Nov 21, 2009 1:59 pm 
Offline

Joined: Sat Feb 21, 2009 11:41 am
Posts: 45
Hey Thanks 2 uuuuuu,
I have found another method n my problem get solved .....

try dis i think u found this good.....
this is for any highest record means N th record........

Code:
SELECT *
FROM Employee E1
WHERE (N-1) = (
SELECT COUNT(DISTINCT(E2.Salary))
FROM Employee E2
WHERE E2.Salary > E1.Salary)
Check it Out Guys,,,,


Top 
 Post subject: Re: Get 2ND Highest Among all
 Post Posted: Sat Nov 21, 2009 1:59 pm 
Offline

Joined: Sat Feb 21, 2009 11:06 am
Posts: 1794
When i tried above on a tab of 97000 records it didnt complete
it was still executing after 11 minutes and i stopped it.
i tried below and it took only 114 ms.

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


Top 
 Post subject: Re: Get 2ND Highest Among all
 Post Posted: Sat Nov 21, 2009 1:59 pm 
Offline

Joined: Sat Feb 21, 2009 11:41 am
Posts: 45
Yea Thanks Man ,,,,,
really it workin fast thanks 4 dat ???


Top 
 Post subject: Re: Get 2ND Highest Among all
 Post Posted: Sat Nov 21, 2009 2:00 pm 
Offline

Joined: Sat Feb 21, 2009 11:06 am
Posts: 1794
You're welcome


Top 
Display posts from previous:  Sort by  
 
 Page 1 of 1 [ 6 posts ] 




Board index » Server Side Development » SQL


Who is online

Users browsing this forum: No registered users and 2 guests

 
 

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
cron