You must Sign In to post a response.
  • Category: LINQ

    Linq query for find the 2nd/3rd/Nth Highest/Lowest salary?

    Can any one tell me the Linq query as well as SQL Query for find the 2nd/3rd/Nth Highest & Lowest salary?
  • #747384
    Hai Sahasra,
    To find the nth lowest or highest salary using SQL Server you can use sub-queries as below:
    nth Highest salary:

    SELECT * FROM EMP e1
    WHERE n =(SELECT Count(DISTINCT(e2.SAL))
    FROM EMP e2
    WHERE e1.SAL <= e2.SAL)

    nth lowest salary:

    SELECT * FROM EMP e1
    WHERE n =(SELECT Count(DISTINCT(e2.SAL))
    FROM EMP e2
    WHERE e1.SAL > e2.SAL)

    using Linq:
    By using Linq, you need to use the Order y and GroupBy(for duplicate) to get it as below:

    var results = empCollection
    .GroupBy(e => e.Sal)
    .OrderByDescending(a => a.Key)
    .Skip(n-1)
    .First();

    Hope it will be helpful to you.

    Regards,
    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
    pawansoftit@gmail.com

  • #747855
    hello
    For Sql Query

    SELECT * FROM Employee emp1
    WHERE n =(SELECT Count(DISTINCT(emp2.SAL))
    FROM Employee emp2
    WHERE emp1.SAL <= emp2.SAL)

    For LINQ
    2nd higest
    List<int> salr = (from ss in dt.AsEnumerable()
    orderby ss.Field<int>("sal") descending
    select ss.Field<int>("sal")).ToList<int>().Skip(n-1).Take(1).ToList<int>();

    If you want to salaries in ascending order then just remove keyword descending from Query.


  • Sign In to post your comments