15,850,446 members
See more:
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```

Code block added - OriginalGriff[/edit]
Posted
Updated 10-Jun-13 22:59pm
v4
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.

## Solution 3

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

manoj s sherje 11-Jun-13 4:14am
thanks sir but not working properly

## Solution 2

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.

v2
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

## Solution 4

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

manoj s sherje 11-Jun-13 4:19am
Sir can you tell me what is CTE

## Solution 5

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.

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

Top Experts
Last 24hrsThis month
 Richard MacCutchan 75 Pete O'Hanlon 70 OriginalGriff 65 k5054 60 Fabrizio Menichelli 10
 OriginalGriff 95 Richard MacCutchan 80 k5054 70 Pete O'Hanlon 70 Dave Kreskowiak 43

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