how to get maximum of two records for each employee

Question

I have a table in the database with records as shown below :

Id | EmpName  | LeaveDate               | createdDate

1  | Govind   | 2014-04-02 00:00:00.000 | 2014-04-02
2  | Aravind  | 2014-04-03 00:00:00.000 | 2014-04-05
3  | Govind   | 2014-04-04 00:00:00.000 | 2014-04-10
4  | Amar     | 2014-04-05 00:00:00.000 | 2014-04-11
6  | Aravind  | 2014-04-06 00:00:00.000 | 2014-04-16
7  | Govind   | 2014-04-07 00:00:00.000 | 2014-04-16
8  | Aravind  | 2014-04-08 00:00:00.000 | 2014-04-16
9  | Amar     | 2014-04-09 00:00:00.000 | 2014-04-16
10 | Aravind  | 2014-04-10 00:00:00.000 | 2014-04-16
11 | Govind   | 2014-04-11 00:00:00.000 | 2014-04-16
12 | Aravind  | 2014-04-12 00:00:00.000 | 2014-04-16
13 | Amar     | 2014-04-13 00:00:00.000 | 2014-04-16
14 | Aravind  | 2014-04-14 00:00:00.000 | 2014-04-16

Now, I want display the last two records of the all employees

Sample output:

Id | EmpName  | LeaveDate                | createdDate


11 | Govind   | 2014-04-11 00:00:00.000 | 2014-04-16
7  | Govind   | 2014-04-07 00:00:00.000 | 2014-04-16
14 | Aravind  | 2014-04-14 00:00:00.000 | 2014-04-16
12 | Aravind  | 2014-04-12 00:00:00.000 | 2014-04-16
13 | Amar     | 2014-04-13 00:00:00.000 | 2014-04-16
9  | Amar     | 2014-04-09 00:00:00.000 | 2014-04-16

Am using JPA, difficult to compose the query for following requirement. Any one help me

`ROW_NUMBER() OVER (PARTITION BY EmpName ORDER BY LeaveDate DESC)`, take result <= 2 — Eric, Sep 22 '15 at 05:59
Just a quick observation, please do NOT use EmpName.... that makes me cry.... use something like EmpID...... — Dryadwoods, Sep 22 '15 at 06:02
am using mysql, sqlserver,oracle and i have implement these 3 DBMS — Thadeuse, Sep 22 '15 at 06:03

Ullas · Answer 1 · 2015-09-22T06:46:26.143

For sql-server.

Using CTE.

;with cte as
(
    select rn = row_number() over
    (
      partition by EmpName
      order by LeaveDate desc
    ),*
from employees
)
select * from cte
where rn <= 2;

SQL Fiddle

For mysql.

By adding a rownumber.

select t2.Id,
t2.EmpName,
t2.LeaveDate,
t2.createdDate
from
(
   select Id,
   EmpName,
   LeaveDate,
   createdDate,
   ( 
       case EmpName 
       when @curA
       then @curRow := @curRow + 1 
       else @curRow := 1 and @curA := EmpName end
    ) + 1 as rn
    from employees t,
    (select @curRow := 0, @curA := '') r
    order by EmpName,LeaveDate desc
)t2
where t2.rn<3;

SQL Fiddle

Below sql query will work for both mysql and sql-server.

Query

select *
from employees t1
where 
(
  select count(*) from employees t2
  where t2.EmpName = t1.EmpName
  and t2.LeaveDate > t1.LeaveDate
) <= 1
order by t1.EmpName;

Nice solution with the `count()` subquery, although I think that's a bit expensive. The inner query refers to the outer, so the inner query is executed for each row of the outer query (that is, for each row in the table). +1 othewise. — gaborsch, Sep 22 '15 at 18:44

Tim Biegeleisen · Answer 2 · 2015-09-22T06:07:10.507

1

Here is a SQL Server query:

SELECT t.Id, t.EmpName, t.LeaveDate, t.createdDate
FROM
(
    SELECT Id, EmpName, LeaveDate, createdDate, RANK() OVER
        (PARTITION BY LeaveDate ORDER BY LeaveDate) num
    FROM Table
) t
WHERE t.num <= 2

edited Sep 22 '15 at 06:07

answered Sep 22 '15 at 06:01

Tim Biegeleisen

502,043
27
286
360

score 1 · Answer 3 · answered Sep 22 '15 at 06:02

use below query which is applicable for Sql Server

;WITH CTE AS 
( SELECT Id , EmpName  , LeaveDate , createdDate, ROW_NUMBER() OVER (PARTITION BY EmpName   ORDER BY ID DESC) RECORD_NUMBER FROM TABLENAME
) SELECT Id , EmpName  , LeaveDate , createdDate FROM CTE WHERE RECORD_NUMBER <=2

score 1 · Answer 4 · edited May 23 '17 at 11:45

1

In JPA (JPQL) it's not possible to solve this issue in a general and efficient way. Rownum concept is so different in databases that in JPQL couldn't model it with different dialects.

Here is one answer that provides a workaround for the whole resultset, but that does not work for subqueries. This answer is about the same story, but they didn't come up with a solution.

Here's an JPQL solution which may be inefficient on large tables (unless you properly index your table):

SELECT e1.EmpName, e1.LeaveDate, e1.createdDate
FROM EmployeeLeaves e1
JOIN EmployeeLeaves e2 on (e1.EmpName = e2.EmpName)
WHERE e1.LeaveDate >= e2.LeaveDate
GROUP BY e1.EmpName, e1.LeaveDate, e1.createdDate
HAVING count(*) <= 2

Another (inefficient) possible solution is to do a query each employee one by one and process the results in Java.

edited May 23 '17 at 11:45

Community

1
1

answered Sep 22 '15 at 07:07

gaborsch

15,408
6
37
48

1

I think that should be a `LEFT JOIN` so as to also get employees with only one record. (Then the `WHERE`clause would have to move to the `ON` clause of course, where I'd place it anyway.) Apart from that this seems to be a feasible solution that circumvents the limitations inflicted by JPA. – Thorsten Kettner Sep 22 '15 at 08:01
@ThorstenKettner `LEFT JOIN` does not matter in this case, because it's a self-join technically (same table on both sides). So, if there's record on the left side, there will be a record on the right side, too. `WHERE` vs `ON` clause: it is question of flavor, both solutions work. – gaborsch Sep 22 '15 at 12:57
1

If only one record exists for empname 'ABC' then you get this record once in e1 and once in e2. You join these and remove the pair in WHERE, because the condition `e1.LeaveDate > e2.LeaveDate` is not met. This leaves you without any match for empname 'ABC', so 'ABC' is not in the results. With an outer join you would select the record in e1, but would not find a record for the same name and older date for e2, so a null reecord for e2 would be joined and you would thus keep your e1 record and this one record for 'ABC' would be in the results. – Thorsten Kettner Sep 22 '15 at 13:37
@ThorstenKettner you're right, but the same applies to the 2-records case, too. The solution is allowing equality for both comparisons. Fixed the query. – gaborsch Sep 22 '15 at 14:15

score 0 · Answer 5 · answered Sep 22 '15 at 06:35

try this query in SQL server

WITH tmp
AS
(
    SELECT id
        ,Empname
        ,Leavedate
        ,CreatedDate
        ,DENSE_RANK() OVER (PARTITION BY Empname ORDER BY Leavedate) AS [rank_leavedate]
    FROM tbl
)

SELECT *
FROM tmp
WHERE tmp.rank_leavedate IN (1,2)

score 0 · Answer 6 · answered Sep 22 '15 at 06:43

On SQL Server Row_Number() function with Partition By clause is best option for such queries

Here is a sample SELECT

with cte as (
    select
    rn = ROW_NUMBER() over (partition by EmpName order by LeaveDate desc),*
    from EmployeeLeaves
)
select
    Id,
    EmpName,
    LeaveDate,
    createdDate
from cte
where rn <= 2

i am me · Answer 7 · 2015-09-22T17:51:01.270

-4

Since the data you provided looks like dummy data I'd re-engineer the database to have one with primary data which would include things like: employee id, employee first name, employee last name, login password, ...

Another table would go by things like: employee id, startdate, leavedate

These two tables would attach to each other on the employee id

You're going to have two while loops, one inside the other: The first sql statement would look similar to:

SELECT * from TBL1

from this you will set a variable name for a query of table 2

The SQL for the second while will look similar to:

SELECT * FROM tbl where employee_id (from table 1)= employee_id (from table 1) LIMIT 2

advantages of this solution is if you're doing millions of records It will have the employee data kept separate from the start, and leave times to save a lot of storage space.

edited Sep 22 '15 at 17:51

answered Sep 22 '15 at 06:05

i am me

89
1
14

your query is incorrect . Poster `want display the last two records of the all employees` – Imran Sep 22 '15 at 06:09
Read the whole answer, not just the SQL. I explained that you're running a while inside a while. How is it spam, it answers the question as generically as possible because they don't provide things like: table names, and what server language they're working with in the application? Webfarm.io is a php development site, as well as a design site. All 3 are correct in their own way. – i am me Sep 22 '15 at 06:11
yes imran i want display last two records of each eployees – Thadeuse Sep 22 '15 at 07:25
_"Webfarm.io is a php development site, as well as a design site."_ What does that have to do with anything? – Cerbrus Sep 22 '15 at 08:45
3

@Cerbrus: There was a comment on this answer calling this answer spam (which, of course, has nothing to do with the answer either). They were responding to that comment, which has since been deleted. (And this is why comment flag handling is such a mess - they never get cleaned up properly.) – BoltClock Sep 22 '15 at 09:13
Some devs think procedural/interface development, and others think in terms of things in terms of just SQL. – i am me Sep 22 '15 at 16:25

how to get maximum of two records for each employee

7 Answers7