how to find second largest number through a query in sql

db , sql United Kingdom
  • 10 years ago

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

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

  • 10 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











  • 10 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)

  • 10 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
     




























  • 10 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)

  • 10 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





























  • 10 years ago

    hi hari,

    iam using 2000

    to get 27 use 4

    to get 26 use5

    to get 25 use7

  • 10 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)


  • 10 years ago

    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
















  • 10 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.

  • 10 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
     

















  • 10 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











  • 10 years ago

    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.

  • 10 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

  • 9 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.

     

  • 9 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

  • 9 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.

     Anyway Thankyou for your toooooo  late reply

     Note:

    Please Don't waste thread...n pls avoide duplicate post..

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

  • 8 years ago

    I tied using the above query.

    But I am getting unexpected results!!!!!

  • 7 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);

  • 7 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

Post a reply

Enter your message below

Sign in or Join us (it's free).

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.

“Never trust a programmer in a suit.” - Anonymous