CODEWITHSUNDEEP
sqlsql-serverselect
user3854793
user3854793

Reputation: 29

Querying the most recently hired employee in each department

Table schema:

create table dept 
( 
     DEPTNO int NOT NULL, 
     DNAME VARCHAR(14), 
     LOC VARCHAR(13)
)

create table emp 
(
     EMPNO int primary key, 
     ENAME VARCHAR(1000), 
     JOB VARCHAR(1000), 
     MGR int, 
     HIREDATE DATETIME, 
     SAL decimal(15, 5), 
     COMM decimal(20, 5), 
     DEPTNO int
)

Deptno in dept and emp table are in primary and foreign key relationship

Question: find the most recently hired employee in each department.

Query: 

SELECT 
    convert(varchar(50), e.ENAME + ',' + e.DEPTNO),
    MAX(hiredate) 
FROM
    emp e  
JOIN
    dept d ON e.deptno = d.deptno 
GROUP BY
    convert(varchar(50), e.ENAME + ',' + e.DEPTNO)

I get an error:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'ashish,' to data type int.

Upvotes: 2

Views: 2821

Answers (2)

Mureinik
Mureinik

Reputation: 311163

If you need any other details about the employee grouping probably isn't the way to go. The rank window function should do the trick:

SELECT *
FROM   (SELECT e.*, 
               dname, 
               RANK() OVER (PARTITION BY e.deptno ORDER BY hiredate DESC) AS rk
        FROM   emp e
        JOIN   dept d ON e.deptno=d.deptno) t
WHERE  rk = 1

Upvotes: 4

shA.t
shA.t

Reputation: 16958

Your error comes from this part; e.ENAME + ',' + e.DEPTNO that you are trying to use + operator to add two different type of fields, So you need to use a correct using of convert like this: e.ENAME + ',' + CONVERT(VARCHAR(50), e.DEPTNO).

But for answering your question you need another type of query like this:

SELECT TOP(1) 
    e.ENAME + ',' + CONVERT(VARCHAR(50), e.DEPTNO)
FROM
    dept d
    LEFT OUTER JOIN
    (SELECT *, ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY HIREDATE DESC) AS recentHired
     FROM emp) e ON d.DEPTNO = e.DEPTNO
ORDER BY
    e.recentHired

Upvotes: 4

Related Questions