Results 1 to 17 of 17

Thread: Find 2nd Highest in sql server

  1. #1
    Junior Member
    Join Date
    May 2007
    Answers
    2

    Exclamation Find 2nd Highest in sql server

    I want to find the 2nd highest number from a list by sql queries.
    i.e What is the query to find the 2nd highest "UnitPrice" from "Products" table in "Northwind" database


  2. #2
    Expert Member
    Join Date
    Dec 2006
    Answers
    204

    Re: Find 2nd Highest in sql server

    select max(UnitPrice)
    from Products
    where UnitPrice < (select max(UnitPrice)
    from Products )


  3. #3
    Junior Member
    Join Date
    Jun 2007
    Answers
    28

    Re: Find 2nd Highest in sql server

    Quote Originally Posted by kumar.santosh View Post
    I want to find the 2nd highest number from a list by sql queries.
    i.e What is the query to find the 2nd highest "UnitPrice" from "Products" table in "Northwind" database
    Can You Try this?
    Select Top 1 Unitprice from(select Top 2 Unitprice from Products Order by Unitprice desc) Products Order by Unitprice


  4. #4
    Expert Member
    Join Date
    Nov 2006
    Answers
    518

    Re: Find 2nd Highest in sql server

    Barbie's query would give the correct reslt

    vcyogi's idea is also correct, but the portion of the command is not required.
    It can be changed as,

    SELECT TOP 1 UnitPrice FROM (SELECT TOP 2 UnitPrice FROM Products ORDER BY UnitPrice DESC);

    Here, the inner query gives only 2 records where UnitPrice is 1st highest and 2nd highest in DECD order, which means the record with 2nd highest UnitPrice comes at the top. Now, the outer query select only the top one.

    Lack of WILL POWER has caused more failure than
    lack of INTELLIGENCE or ABILITY.

    -sutnarcha-

  5. #5
    Junior Member
    Join Date
    Jun 2007
    Answers
    28

    Re: Find 2nd Highest in sql server

    Can you retrive more than one 2'nd highest salary?? example emp table ename salary aaaa 1000 bbbb 2000 cccc 3000 dddd 2000 can you retrive two records 'bbbb' & 'dddd' for 2'nd highest salary in a single query??


  6. #6
    Expert Member
    Join Date
    Nov 2006
    Answers
    518

    Re: Find 2nd Highest in sql server

    Select * from emp where sal= (select max(sal) from emp where sal< (select max(sal) from emp)); this looks like the same command put multiple times between = and <.:d but it works. this is an indirect method. There can be a direct query for the same. I will have to come back to this thread with it.

    Lack of WILL POWER has caused more failure than
    lack of INTELLIGENCE or ABILITY.

    -sutnarcha-

  7. #7
    Junior Member
    Join Date
    Jun 2007
    Answers
    28

    Re: Find 2nd Highest in sql server

    I find the Query using WITH TIES keyword in Sql

    select top 1 with ties eno from (select top 2 with ties eno
    from emp order by eno desc) emp order by eno

    Note:With Ties Returns the similar record of resulted record


  8. #8
    Junior Member
    Join Date
    May 2007
    Answers
    16

    Re: Find 2nd Highest in sql server

    Barbie's query would give the correct reslt
    Sutnarcha , barbies query will give you correct results no doubt .
    But VCyogi has given you a generic / more flexible query with wich you can get n-th higest / lowset salaray. It is just the matter of changing the n value in inner query and altering the sort order.


  9. #9
    Expert Member
    Join Date
    Nov 2006
    Answers
    518

    Re: Find 2nd Highest in sql server

    Hay, that's good man !!!

    Keep up the spirit VCYogi

    SQLTweety,
    I know see that VCYogi's query is more flexible. Thanx for helping me see the difference.

    Lack of WILL POWER has caused more failure than
    lack of INTELLIGENCE or ABILITY.

    -sutnarcha-

  10. #10
    Junior Member
    Join Date
    Jul 2007
    Answers
    5

    Re: Find 2nd Highest in sql server

    SELECT top 1 UnitPrice FROM (SELECT TOP 2 UnitPrice FROM Products ORDER BY UnitPrice DESC)


  11. #11
    Junior Member
    Join Date
    Jul 2007
    Answers
    5

    Re: Find 2nd Highest in sql server

    SELECT UnitPrice where rownum<2 FROM (SELECT TOP 2 UnitPrice FROM Products ORDER BY UnitPrice );


  12. #12
    Junior Member
    Join Date
    Jul 2007
    Answers
    1

    Re: Find 2nd Highest in sql server

    select min(unit price ) from products where unitprice in (select top 2 unit price from products order by unitprice desc)


  13. #13
    Contributing Member
    Join Date
    Apr 2007
    Answers
    58

    Re: Find 2nd Highest in sql server

    Very helpful thread...Now, I learned two new sql commands...TIE and TOP...Thanks

    Regards,
    Anoop :)
    If its useful, dont forget to [COLOR="Red"]THANK[/COLOR] me :cool:

  14. #14
    Junior Member
    Join Date
    Oct 2007
    Answers
    1

    Re: Find 2nd Highest in sql server

    Select Top N sal from employee where sal in (select distinct sal from employee order by sal desc)

    In the above query N represents the nth highest salary.. I think this help ur problem easily...:-)


  15. #15
    Junior Member
    Join Date
    Apr 2006
    Answers
    3

    Re: Find 2nd Highest in sql server

    Quote Originally Posted by kumar.santosh View Post
    I want to find the 2nd highest number from a list by sql queries.
    i.e What is the query to find the 2nd highest "UnitPrice" from "Products" table in "Northwind" database
    select top 1 * from (select top 2 * from Products order by UnitPrice desc) table1 order by Id asc.....


  16. #16
    Junior Member
    Join Date
    Nov 2007
    Answers
    5

    Re: Find 2nd Highest in sql server

    Select top 1 price from (select top 2 price from products order by price desc);


  17. #17
    Junior Member
    Join Date
    Dec 2007
    Answers
    1

    Re: Find 2nd Highest in sql server

    select * from ( select dense_rank() over( order by unitprice desc ) as rownum, * from products ) p where p.rownum = 2


  18.  Sponsored Ads
    Ad


     

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact