Click here to Skip to main content
15,850,446 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
how to find eight heights salary from following table we have two table Employee_Table and salary table

Employee_Table                 
field name                      
ID Name designation                
1  ab     cd                                    
2  bc     ce                        
3  ef     fd                        
4  vc     fd                       
5  vd     bv                      
6  df     de                        
7  fg     rt                       
8  gf     tg                       


Salary_Table
 field name
SalID Salary EmpID 
1     1200   1   
2     1255   2
3     2500   3
4     2600   4
5     2100   6
6     2500   5 
7     3000   8
8     4000   7


[edit]Code block added - OriginalGriff[/edit]
Posted
Updated 10-Jun-13 22:59pm
v4
Comments
John-ph 11-Jun-13 3:54am    
Do you mean highest, top 8 salary?
Abhinav S 11-Jun-13 3:57am    
YOu can use Id as a where clause in your query.

Try this:
SQL
SELECT TOP 1 Salary
FROM (
SELECT DISTINCT TOP 8 
e.Name, 
e.Designation, 
s.Salary 
FROM Employee_Table e
JOIN Salary_Table s
ON e.ID = s.EmpID
ORDER BY s.Salary DESC) a
ORDER BY Salary
 
Share this answer
 
Comments
manoj s sherje 11-Jun-13 4:14am    
thanks sir but not working properly
 
Share this answer
 
Try:
SQL
SELECT TOP 8 e.Name, e.Designation, s.Salary FROM Employee_Table e
JOIN Salary_Table s
ON e.ID = s.EmpID
ORDER BY s.Salary



[EDIT]
Don't try it quite like that - you want to start with a JOIN to force the two tables together on the relevant data - the Employee ID:
Then, you need to order your rows, and provide a row number - otherwise it's not a "highest" value!

Try this:

SQL
SELECT Name, Designation, Salary FROM
    (SELECT e.Name, e.Designation, s.Salary , ROW_NUMBER() OVER (ORDER BY s.Salary DESC) AS R FROM Employee_Table e
    JOIN Salary_Table s
    ON e.ID = s.EmpID) AS A
WHERE A.R=3

It's pretty simple, if you look at the bits:
SQL
SELECT e.Name, e.Designation, s.Salary , ROW_NUMBER() OVER (ORDER BY s.Salary DESC) AS R FROM Employee_Table e
JOIN Salary_Table s
ON e.ID = s.EmpID
All this does is say that the two tables are linked together by the Employee ID, and that I want the row number of each row ordered by salary, highest first. The "e" and "s" bits just proved shorter names for the tables, so I don't have to type Employee_Table and Salary_Table all the time!

The outer SELECT then specifies exactly what you want to return - the Third row only, and the relevant info from that row.
 
Share this answer
 
v2
Comments
manoj s sherje 11-Jun-13 4:14am    
thanks sir but not working properly
OriginalGriff 11-Jun-13 4:27am    
That's not a lot of help, as error reports go.
What is it doing that you don't expect it to, or not doing that you do? :laugh:
manoj s sherje 11-Jun-13 4:47am    
sorry sir but i ma trying my self i ma find third highest salary in one table
exp: SELECT * FROM Employee e1 WHERE (2) = (SELECT COUNT(DISTINCT(e2.Salary))
FROM Employeee2 WHERE e2.Salary > e1.Salary)

but i am trying two table using sub query

select E.EmpID,E.Name,E.Designation,Max(S.Salary)
FROM dbo.Employee E, dbo.Salary S
WHERE S.EmpID = E.EmpID not in (SELECT TOP 9 S.Salary FROM dbo.Salary S)
OriginalGriff 11-Jun-13 5:30am    
Answer updated
SQL
with CTE AS (select *,ROW_NUMBER() over (order by salary desc) as Number from Employee_Table et inner join Salary_Table st on et.ID = st.EmpID)

select * from CTE where number = 8
 
Share this answer
 
Comments
manoj s sherje 11-Jun-13 4:19am    
Sir can you tell me what is CTE
SQL
with CTE AS (select *,ROW_NUMBER() over (order by salary desc) as Number from Employee_Table et inner join Salary_Table st on et.ID = st.EmpID)

select * from CTE where number = 8


CTE stands for common table expression and this is a syntax of using it.I have written CTE, you can write any word there. Please let me know if this query helps.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900