Click here to Skip to main content
15,302,859 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
i have two table one is employee which have two column id, name and second table salary have id,salary,designation. How to find the second highest salary with name

What I have tried:

i try to inner join with where clause but not getting appropriate result
Posted
Updated 28-Mar-18 23:16pm
Comments
Maciej Los 28-Mar-18 14:32pm
   
What's database name and version?
[EDIT]
What have you tried? Show us your try.

First, write a query to get the highest salary with it's Id:
SQL
SELECT TOP 1 Id, Salary FROM 
   (SELECT TOP 2 Salary, Id FROM Salary ORDER BY Salary DESC) AS m 
ORDER BY Salary ASC

Then JOIN that to the other table t pick up the name.
   
Comments
Maciej Los 29-Mar-18 5:17am
   
5ed!
CPallini 29-Mar-18 5:20am
   
5.
The inner join is the way to go. Sqlite example:
SQL
BEGIN TRANSACTION;

CREATE TABLE EMP(Id integer PRIMARY KEY, Name text);
CREATE TABLE SAL(EmpId integer, SALARY Integer);

INSERT INTO EMP VALUES(1,'Foo');
INSERT INTO EMP VALUES(2,'Boo');
INSERT INTO EMP VALUES(3,'Goo');

INSERT INTO SAL VALUES(1, 10000);
INSERT INTO SAL VALUES(2, 7000);
INSERT INTO SAL VALUES(3, 9000);

COMMIT;

SELECT EMP.Name, SAL.SALARY FROM EMP JOIN SAL ON EMP.Id=SAL.EmpId ORDER BY SAL.SALARY LIMIT 1 OFFSET 1;
   
Comments
Maciej Los 29-Mar-18 5:17am
   
5ed!
For MS SQL Server, you may use Ranking Functions (Transact-SQL) | Microsoft Docs[^], especially: ROW_NUMBER (Transact-SQL) | Microsoft Docs[^]

SQL
SELECT t.*
FROM (
    SELECT e.name, s.salary, ROW_NUMBER() OVER(ORDER BY salary DESC) AS rn
    FROM employee AS e INNER JOIN salary AS s ON e.ID = s.empID
) AS t
WHERE t.rn=2


See past answer: How to get employee names along department names who having highest salary in respective departments.[^]
   
v2
Comments
CPallini 29-Mar-18 5:19am
   
5.
Maciej Los 29-Mar-18 5:30am
   
Thank you, Carlo.

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