What is wrong in this sql select statement?

Question

Here is my select statement,

SELECT TOP 1
  EmpId, RemainingAdvance 
FROM SalaryDetails
WHERE EmpId IN (SELECT Emp_Id
                FROM Employee
                WHERE Desig_Id='27')
ORDER BY CreatedDate DESC

When i executed SELECT Emp_Id FROM Employee WHERE Desig_Id='27' the results were

Emp_Id
16
17

But when i execute my first statement it gives me result for only 16 but no output 17... I have records for both EmpId's in SalaryDetails Table.....

EDIT:

Removing TOP 1 from my query i got this,

SELECT EmpId, RemainingAdvance FROM SalaryDetails 
where EmpId in (select Emp_Id from Employee where Desig_Id='27')
               ORDER BY CreatedDate DESC 

gave me

I want results for EmpId 16,17 ORDER BY CreatedDate DESC... Because my now my Desig_Id='27' and i will change it with a variable @CategoryId ... So there may be 'n' number of employees based on @CategoryId

EmpId  RemainingAdvance
16                354.00
17                 0.00

Answer 1

SELECT TOP 1 returns only one row.

You could try to use SELECT TOP 10 instead.

EDIT:

You always get 16, because it seems to have the higher CreatedDate, and you sort by that column descending.

Answer 2

Select Top 1 will select one top row. Lose the top 1 if you want to see all of them

Answer 3

SQL Server 2005+, Using CTE and ROW_NUMBER:

---

WITH summary AS (
    SELECT sd.empid,
           sd.remainingadvance,
           ROW_NUMBER() OVER (PARTITION BY sd.empid ORDER BY sd.createddate DESC) AS rank
      FROM SALARYDETAILS sd
      JOIN EMPLOYEE e ON e.emp_id = sd.empid
                     AND e.desig_id = '27')
SELECT s.empid,
       s.remainingadvance
  FROM summary s
 WHERE s.rank = 1

SQL Server 2005+, Non-CTE Equivalent

---

SELECT s.empid,
       s.remainingadvance
  FROM (SELECT sd.empid,
               sd.remainingadvance,
               ROW_NUMBER() OVER (PARTITION BY sd.empid ORDER BY sd.createddate DESC) AS rank
          FROM SALARYDETAILS sd
          JOIN EMPLOYEE e ON e.emp_id = sd.empid
                         AND e.desig_id = '27') s
 WHERE s.rank = 1

Reference:

• ROW_NUMBER

Answer 4

SELECT SD.EmpId, SD.RemainingAdvance 
FROM 
SalaryDetails SD INNER JOIN 
   (SELECT SD2.EmpID, MAX(SD2.CreatedDate) AS MAXDate 
   FROM SalaryDetails SD2 GROUP BY SD2.EmpID) AS SD2
ON SD.EmpID = SD2.EmpID
INNER JOIN Employee E
ON SD.EmpID = E.EmpID
WHERE E.Desig_Id = '27' AND SD.CreatedDate = SD2.MaxDate

Note: The query is written without trying it out.
I suppose, this is what you are looking for.

---

This variant also works (in at least one DBMS - namely IBM Informix Dynamic Server 11.50):

SELECT SD.EmpId, SD.RemainingAdvance 
  FROM SalaryDetails SD
       INNER JOIN 
       (SELECT SD2.EmpID, MAX(SD2.CreatedDate) AS MAXDate 
          FROM SalaryDetails SD2 GROUP BY SD2.EmpID) AS SD2
       ON SD.EmpID = SD2.EmpID AND SD.CreatedDate = SD2.MaxDate
       INNER JOIN Employee E
       ON SD.EmpID = E.EmpID
 WHERE E.Desig_Id = '27'

The compound join in the first ON clause might improve the performance of the query - but it is quite possible that the optimizer would hoist the 'AND SD.CreatedDate = SD2.MaxDate' condition anyway, meaning you wouldn't spot any difference even if you examined the two query plans. I'm not quite sure of the best way to indent the table expressions after the main FROM clause.

Edited by Jonathan Leffler - as requested by Shahkalpesh.

Answer 5

The statement performs as it is told you, but not as you intended to.

I guess you want to have the most recent SalaryDetails record for each Employee with a certain Desig_ID:

SELECT EmpId, RemainingAdvance
FROM SalaryDetails
WHERE CreatedDate IN
    (SELECT MAX(d.CreatedDate)
     FROM SalaryDetails d
     INNER JOIN Employee e ON d.EmpId = e.EmpId
     WHERE e.Desig_Id = '27'
     AND SalaryDetails.EmpId = d.EmpId
    )

Answer 6

This works, but the correlated sub-query is not efficient:

CREATE TABLE employee
(
    empid INTEGER NOT NULL PRIMARY KEY,
    desig_id CHAR(2) NOT NULL
);

INSERT INTO employee VALUES(16, '27');
INSERT INTO employee VALUES(17, '27');
INSERT INTO employee VALUES(15, '13');
INSERT INTO employee VALUES(18, '9');

CREATE TABLE salarydetails
(
    empid INTEGER NOT NULL REFERENCES employee,
    createdate DATE NOT NULL,
    PRIMARY KEY (empid, createdate),
    remainingAdvance DECIMAL(10,2) NOT NULL
);

INSERT INTO salarydetails VALUES (15, '2009-12-13', 1534.00);
INSERT INTO salarydetails VALUES (16, '2010-01-31', 3634.00);
INSERT INTO salarydetails VALUES (16, '2010-02-14', 2634.00);
INSERT INTO salarydetails VALUES (17, '2010-01-03', 5734.00);
INSERT INTO salarydetails VALUES (17, '2010-02-03', 4734.00);
INSERT INTO salarydetails VALUES (17, '2010-03-01', 3734.00);
INSERT INTO salarydetails VALUES (18, '2010-01-13', 5834.00);

SELECT s1.empid, s1.remainingAdvance
FROM SalaryDetails AS s1
WHERE s1.empid IN (SELECT e.empid FROM employee AS e WHERE e.desig_id = '27')
  AND s1.createdate = (SELECT MAX(s2.createdate)
                          FROM salarydetails AS s2
                         WHERE s2.empid = s1.empid);

Result:

EmpID       RemainingAdvance
   16                2634.00
   17                3734.00