Click here to Skip to main content
15,892,927 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
For a table created using the following commands. State the SQL SELECT command to find the 1st and 2nd highest salary earned by staff working in DEL or MUM
SQL
CREATE TABLE Employees (
    ID int NOT NULL AUTO_INCREMENT,
    Name varchar(100),
    Sal decimal (10,2),
    City char (3),
    PRIMARY KEY (ID)
);

INSERT INTO Employees (Name, Sal, City) VALUES ('Ramesh',20000, 'BLR');
INSERT INTO Employees (Name, Sal, City) VALUES ('Sunil',24000, 'DEL');
INSERT INTO Employees (Name, Sal, City) VALUES ('Sreeja',21000, 'MUM');
INSERT INTO Employees (Name, Sal, City) VALUES ('Pavan',23000, 'DEL');
INSERT INTO Employees (Name, Sal, City) VALUES ('Maya',24000, 'MUM');
Posted
Updated 18-Jan-14 5:18am
v3
Comments
Member 10532589 18-Jan-14 11:21am    
please reply soon

We do not do your homework: it is set for a reason. It is there so that you think about what you have been told, and try to understand it. It is also there so that your tutor can identify areas where you are weak, and focus more attention on remedial action.

Try it yourself, you may find it is not as difficult as you think!

I'll give you a hint though: SELECT has an optional TOP specifier, and can ORDER BY columns...
 
Share this answer
 
Comments
Jörgen Andersson 18-Jan-14 11:47am    
I wouldn't count on the TOP clause existing on a MySQL or MariaDB server ;-)
OriginalGriff 18-Jan-14 11:59am    
It's not my homework! :laugh:
I suggest to use ranking functions[^].

For example:
SQL
SELECT RowNo, [Name], Sal
FROM (
    SELECT ROW_NUMBER() OVER(PARTITION BY City ORDER BY Sal DESC) AS RowNo, [Name], Sal
    FROM Employee
    WHERE City IN ('DEL','MUM') 
) AS T
WHERE RowNo IN (1,2)


NOTE: Depending on your needs, you should use proper ranking function.
 
Share this answer
 
Comments
Christian Graus 18-Jan-14 17:56pm    
I prefer not to give copy and paste answers to people who have obviously posted their homework
Ignore the other answers you got. One is correct, but over complicated and your teacher will know you cheated, and the other is just stupid and wrong. Read OriginalGriff's answer. It's really easy to do, with basic SQL.

Here[^] is my article on select statements. Work through it. Learn SQL and pass your course. Don't try to cheat and end up as clueless as you started. You won't find a job that way.
 
Share this answer
 
You can get the first and the second numbers in a table quite easily.
Even without TOP, its not very difficult to do.

Hint: Based on OriginalGriff's homework pointer, pick the top number and then pick the top number.
 
Share this answer
 
Below is your TSQL:

SQL
SELECT
    TOP 2 *
FROM
    Employees
WHERE
    City IN ('DEL','MUM')
 
Share this answer
 
Comments
Christian Graus 18-Jan-14 17:56pm    
Amusingly, this is plain wrong :-)

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