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

    How to get Get the highest salary without using TOP clause


    Are you looking for a way to Get the highest salary without using TOP clause ? then read this thread to know how to get it



    Hi All,

    Can any one please send me the answer of below question.

    1. Get the nth highest salary without using TOP clause.
    2. Why Having clause if we have the Where clause to filter the records.
    3. Can we use the where clause with having clause
    4. Is the Order By clause must for using the Having clause?
    5. How the indexes works internally.
    6. Why more than one Clustered index not allowed for a table.
    7. Do you think that having more indexes degrades the performance of the query? Id so then why?
  • #743995
    Answers

    1) SELECT *
    FROM Employee Emp1
    WHERE (N-1) = (
    SELECT COUNT(DISTINCT(Emp2.Salary))
    FROM Employee Emp2
    WHERE Emp2.Salary > Emp1.Salary)

    2) In Sql Where filters data on row level(before grouping) but haiving filters data (after grouping) group by.

    3)no u cant

    4)no

    5) How an INDEX Works ?

    The database takes the columns specified in a CREATE INDEX command and sorts the values into a special data structure known as a B-tree. A B-tree structure supports fast searches with a minimum amount of disk reads, allowing the database engine to quickly find the starting and stopping points for the query we are using.


    Conceptually, we may think of an index as shown in the diagram below. On the left, each index entry contains the index key (UnitPrice). Each entry also includes a reference (which points) to the table rows which share that particular value and from which we can retrieve the required information.


    Much like the index in the back of a book helps us to find keywords quickly, so the database is able to quickly narrow the number of records it must examine to a minimum by using the sorted list of UnitPrice values stored in the index. We have avoided a table scan to fetch the query results. Given this sketch of how indexes work, lets examine some of the scenarios where indexes offer a benefit.

    6)

  • #744021
    Highest salary :
    Use MAX method of sql it will give you highest salary from the table.
    see below snippet

    Select Max(sal) from emp


    Having clause:
    There are number of reason behind having clause, this clause introduces a condition on aggregations, the same thing can not done by 'where' clause. e.g. 'COUNT' can not be used with WHERE but can be used with 'HAVING'
    In most of the cases HAVING used after 'Group By' and 'where' used before 'Group By'

    Order by for Having:
    Order by clause must not for 'having' class, as we can use Order by clause separately too

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #744035
    HI..



    1. Find the nth Highest salary

    DECLARE @N int
    SET @N = 3 -- Change the value here to pick a different salary rank

    SELECT Salary
    FROM (
    SELECT row_number() OVER (ORDER BY Salary DESC) as SalaryRank, Salary
    FROM Salaries
    ) as SalaryCTE
    WHERE SalaryRank = @N

    2. Where is used for filtering the rwos and columns. But the Having is used ro filtering
    after Group by clause

    3. Yes

    4.No.

    5. Refer the Below mentioned Link:

    http://stackoverflow.com/questions/5402112/indexes-in-sql-server-internal-working-and-structure-of-indexes

    6. Refer the below mentioned Links:
    1.http://www.sqlservercentral.com/Forums/Topic1401144-391-1.aspx
    2.http://stackoverflow.com/questions/21456971/why-only-one-clustered-index-per-table-should-be-created-in-sql-server

  • #744040
    1) SELECT *
    FROM Employee Emp1
    WHERE (N-1) =
    SELECT COUNT(DISTINCT(Emp2.Salary))
    FROM Employee Emp2
    WHERE Emp2.Salary > Emp1.Salary)

    2)Though both WHERE and HAVING clause is used to specify filtering condition in SQL if both WHERE and HAVING clause is used in a SELECT query with aggregate function or GROUP BY clause, it will execute before HAVING clause. condition specified in WHERE clause is used while fetching rows from table, and data which doesn't pass the condition will not be fetched into result set, on the other hand HAVING clause is later used to filter summarized data or grouped data
    3)yes explained in second what happens if both are used together
    4)No
    5)http://www.codeproject.com/Articles/39006/Overview-of-SQL-Server-Table-Indexing-Pa
    6)Because Clustered Index does the physical sorting and it is not possible for multiple one
    7)http://dba.stackexchange.com/questions/17830/will-more-indexes-on-a-table-affect-performance

  • #744063
    Select Max(Sal) from tablename


    this will give the Top salary in table.

    Thanks & Regards
    Anil Kumar Pandey
    Microsoft MVP, DNS MVM


  • Sign In to post your comments