Вопрос по sql – Как получить последнюю запись сотрудника в оракуле?

1

Error: User Rate Limit Exceeded

Таблица сотрудников может содержать несколько записей для одного сотрудника с разными датами вступления в силу и статусами.

Сотрудник может иметь два статуса «Leaver». и "Столяр".

id     emp_id    firstname     region    status     effective_date  
1       1         James        Asia      Joiner     1-Jan-2012 
2       1         James        UK        Leaver     1-Aug-2012
3       1         James        USA       Joiner     1-Aug-2012
4       1         James        Asia      Leaver     1-May-2012
5       1         James        UK        Joiner     1-May-2012
6       1         James        USA       Leaver     1-Sep-2012

Error: User Rate Limit Exceeded

Error: User Rate Limit Exceeded

Error: User Rate Limit Exceeded

Error: User Rate Limit Exceeded

Error: User Rate Limit Exceeded

SELECT 
        emp_id, 
        MAX(effective_date) AS latest_effective_date
FROM 
        EMPLOYEE
GROUP BY 
        emp_id

Error: User Rate Limit Exceeded

Если я помещу их в предложение выбора или группу по предложению, я получу не только последнюю запись, но и другие записи.

Error: User Rate Limit Exceeded Sebas
Error: User Rate Limit Exceeded Sebas
Error: User Rate Limit Exceeded ashishjmeshram

Ваш Ответ

5   ответов
0

Error: User Rate Limit Exceeded

SELECT *
FROM EMPLOYEE emp
INNER JOIN (SELECT max(id) AS id
        emp_id, 
        MAX(effective_date) AS latest_effective_date
FROM 
        EMPLOYEE
GROUP BY 
        emp_id) AS employee_1 on emp.id = employee_1.id
Error: User Rate Limit Exceeded ashishjmeshram
1

Error: User Rate Limit Exceeded

2       1         James        Asia      Leaver     1-May-2012
3       1         James        UK        Joiner     1-May-2012

Error: User Rate Limit Exceeded

Error: User Rate Limit Exceeded

9
SELECT * FROM 
( SELECT  
    e.*,
    ROW_NUMBER() OVER (partition by emp_id order by effective_date DESC) r
FROM  
    EMPLOYEE  e)
WHERE r = 1;

Error: User Rate Limit Exceeded

Error: User Rate Limit Exceeded

Error: User Rate Limit Exceeded

 SELECT * FROM 
( SELECT  
    e.*,
    ROW_NUMBER() OVER (partition by emp_id order by effective_date DESC, status ASC) r
FROM  
    EMPLOYEE  e
WHERE effective_date <= '<your desired date>')
WHERE r=1;
2

Error: User Rate Limit Exceeded

SELECT  Emp.*
FROM    Employee Emp
        INNER JOIN
        (   SELECT  Emp_ID, MAX(effective_date) AS latest_effective_date
            FROM    Employee
            GROUP BY Emp_ID
        ) MaxEmp
            ON Emp.Emp_ID = MaxEmp.Emp_ID
            AND Emp.Effective_Date = MaxEmp.latest_effective_date
Error: User Rate Limit Exceeded
Error: User Rate Limit Exceeded ashishjmeshram
1

Error: User Rate Limit Exceeded

SELECT  
  MAX(id) KEEP (DENSE_RANK FIRST ORDER BY effective_date DESC) id,
  MAX(emp_id) KEEP (DENSE_RANK FIRST ORDER BY effective_date DESC) emp_id,
  MAX(firstname) KEEP (DENSE_RANK FIRST ORDER BY effective_date DESC) firstname,
  MAX(status) KEEP (DENSE_RANK FIRST ORDER BY effective_date DESC) status,
  MAX(effective_date) KEEP (DENSE_RANK FIRST ORDER BY effective_date DESC) effective_date
FROM Employee GROUP BY firstname

Похожие вопросы