Rules are:
In case COMM_TYP is filled for the records.
COMM_TYP X is first priority , Y is next priority and Z is the last priority
In case a Employee having 2 records are having same COMM_TYP , then next priority is the LINE_NR(EMP_NR is 2 in this case).That is highest LINE_NR record of the employee has to be picked for result.
Result of the above table looks like below and delete other not matching rows from the table : EMP_COMM
Joined: 26 Nov 2002 Posts: 12367 Topics: 75 Location: San Jose
Posted: Thu Mar 08, 2018 3:41 pm Post subject:
me_naveen,
Try the following SQL which will give you the desired results.
Code:
SELECT A.EMP_NR
,A.LINE_NR
,A.COMP_TYP
,A.COM_M_ID
FROM your_table A
,(SELECT EMP_NR AS EMP_NR
,MIN(COMP_TYP) AS COMP_TYP
,MAX(LINE_NR) AS LINE_NR
,ROWNUMBER()
OVER (PARTITION BY EMP_NR) AS ROW#
FROM your_table
GROUP BY EMP_NR,COMP_TYP) B
WHERE A.EMP_NR = B.EMP_NR
AND A.LINE_NR = B.LINE_NR
AND A.COMP_TYP = B.COMP_TYP
AND A.COMP_TYP > ' '
AND B.ROW# = 1
;
PS: Start using the code tags in your posts so that it retains the spacing of the data. If your are not familiar with code tags then check this link explains in detail about code tags
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
