Menu
I have problems finding a solution to an SQL query. This is probably a very obvious beginner question but I can't seem to get the results that I'm after. I have a table that looks something like the following:
|Name |Station|Salary|
|Bob |1 |2000 |
|Steve|2 |1750 |
|Mark |3 |2050 |
|Lisa |4 |2200 |
|Hans |5 |2000 |
I would like to select the names of the people in this table that have the same salary. The result should of course be Bob and Hans.
349
Best Answer select rnk, last_name, department_id, salary from ( select last_name, department_id, salary, RANK () OVER ( PARTITION BY department_id ORDER BY salary DESC ) AS rnk from employees ) where rnk <= 3 ; You still need a sub-query, because analytic functions are computed after the WHERE clause is applied.
The SELF JOIN in SQL, as its name implies, is used to join a table to itself. This means that each row in a table is joined to itself and every other row in that table. However, referencing the same table more than once within a single query will result in an error. To avoid this, SQL SELF JOIN aliases are used.
Below is simple query to find the employee whose salary is highest. select *from employee where salary=(select Max(salary) from employee);
SELECT Name
FROM table1
WHERE Salary IN (
SELECT Salary
FROM table1
GROUP BY Salary
HAVING COUNT(*) > 1
)
If you join the table against itself on Salary but where the names are separate then this should give you any matching salaried people:
SELECT s1.Name, s1.Station, s1.Salary
FROM Staff s1
INNER JOIN Staff s2 ON s1.Salary = s2.Salary AND s1.Name <> s2.Name
Here's a SQLFiddle to show it in action
36
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
