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...
-
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 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 [AMST].[DBO].EmpMaster E1
Where 2=(Select Count(*) From [AMST].[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 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 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
Where t1.Age<=t2.Age)
--Drop table tmptable
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.
-
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 EMPWHERE
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 20krish
-
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
-
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.
Anyway Thankyou for your toooooo late reply
Note:
Please Don't waste thread...n pls avoide duplicate post..
Regards
Hari K.......
-
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
// -
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......
-
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
ascending order):
select distinct(num) from data d1 where 2=(select
count(distinct(num)) from data d2 where d1.num>d2.num);
-
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);
-
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
Post a reply
SQL forum discussion
-
PROBLEM
by royal ludhiana (2 replies)
-
Sql Update Trigger
by ElviOliv (2 replies)
-
SQL server
by raf9 (0 replies)
-
lower and upper limit problem
by ansari.wajid (2 replies)
-
Pls help me, thank you!
by joe90 (1 replies)
Quick links
Recent activity
- 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...
Enter your message below
Sign in or Join us (it's free).