Click here to Skip to main content
12,406,342 members (71,390 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL
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
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 18-Jan-14 5:16am
Updated 18-Jan-14 5:18am
v3
Comments
Member 10532589 18-Jan-14 11:21am
   
please reply soon
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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...
  Permalink  
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:
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 4

I suggest to use ranking functions[^].

For example:
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.
  Permalink  
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
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 5

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.
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

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.
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

Below is your TSQL:

SELECT
    TOP 2 *
FROM
    Employees
WHERE
    City IN ('DEL','MUM')
  Permalink  
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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160730.1 | Last Updated 18 Jan 2014
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100