Removing last row of a grouped row in a query result

Question

0.00/5 (No votes)

See more:

Hello all,

I want to return a resulting query from a table with the following requirements.
(a) It has to have records more than 1 of the column : EmployeeId
(b) If it has records more than 1 then the record with the lowest BeginDate record shouldn't be on the resulting query.

Just to give you an example;
My Records:

Company	EmployeeID	StartDate	EndDate
ComA	1	01/08/1995	31/08/2013
ComA	1	01/09/2013	31/03/2014
ComA	1	01/04/2014	01/01/2100
ComB	1	01/09/2008	17/02/2009
ComC	1	22/05/1995	18/03/2014
ComC	1	19/03/2014	01/01/2100
ComA	2	14/01/2006	24/06/2009
ComA	2	25/06/2009	01/01/2100
ComB	2	30/11/2010	13/12/2011
ComB	2	14/12/2011	01/01/2100
ComC	2	25/07/2001	01/05/2006
ComC	3	07/07/2010	11/12/2012
ComC	3	12/12/2012	01/01/2100

The final should be

Company	EmployeeID	StartDate	EndDate
ComA	1	01/09/2013	31/03/2014
ComA	1	01/04/2014	01/01/2100
ComC	1	19/03/2014	01/01/2100
ComA	2	25/06/2009	01/01/2100
ComB	2	14/12/2011	01/01/2100
ComC	3	12/12/2012	01/01/2100

I have accomplished the requirement (a) by the following query but I am stuck at requirement (b)
Can someone help me thanks in advance.

SELECT P1.*
FROM Employee P1
INNER JOIN
(SELECT EmployeeID
FROM Employee
GROUP BY EmployeeID
HAVING COUNT(EmployeeID) > 1
ORDER BY EmployeeID) P2
ON P1.EmployeeID = P2.EmployeeID
ORDER BY P1.EmployeeID, P1.BeginDate DESC;

Posted 20-Aug-14 7:04am

wonder-FOOL

Add a Solution

Comments

PhilLenoir 20-Aug-14 15:04pm

Do you have or can you add a unique ID column to the table. If so a correlated subquery will work based on the unique ID.

wonder-FOOL 21-Aug-14 2:29am

The unique id is combination of Company and EmployeeID. There might be same EmployeeID's but Company will make it distinct from one to another.

2 solutions

Solution 1

This may not be the prettiest code and there may be some optimizations that you could do. I used SELECT * because you did, but I'd advocate using field lists to avoid schema queries being run by the DBMS before running the main query:

SELECT P3.* FROM( SELECT P1.*, MIN(StartDate) MinDate
FROM Employee P1
INNER JOIN
(SELECT EmployeeID
FROM Employee
GROUP BY EmployeeID
HAVING COUNT(EmployeeID) > 1
ORDER BY EmployeeID) P2
ON P1.EmployeeID = P2.EmployeeID) P3
WHERE MinDate <> StartDate
ORDER BY P3.EmployeeID, P3.BeginDate DESC;

Posted 20-Aug-14 9:32am

PhilLenoir

Comments

wonder-FOOL 21-Aug-14 2:28am

Thank you very much for your help but I am getting an grouping error on the query.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

**Orcun Iyigun** · Accepted Answer · 2014-08-20T20:53:00

Basically whenever SQL query starts new group it should start numbering to each row of that group which is EmployeeID and use the ROW_NUMBER function to assign the numbers. For more documentation check: ROW_NUMBER[^]

SQL

SELECT T.Company, T.EmployeeID, T.StartDate, T.EndDate, T.GROUPEDROWNUM
FROM
(
  SELECT P1.Company, P1.EmployeeID, P1.StartDate, P1.EndDate, ROW_NUMBER() OVER (PARTITION BY P1.EmployeeID ORDER BY P1.StartDate) AS GROUPEDROWNUM
  FROM Employee P1
  INNER JOIN 
  (SELECT EmployeeID
   FROM Employee
   GROUP BY EmployeeID
   HAVING COUNT(EmployeeID) > 1   
  ) P2
  ON P1.EmployeeID = P2.EmployeeID  
) T
WHERE T.GROUPEDROWNUM > 1
ORDER BY T.Company, T.EmployeeID, T.StartDate ASC

Good luck,
OI