Loading only records with largest revision number

[Anonymous]

I have a set of records with revision number as shown below :

SN	REQ_NO	REV_NO	Department
1	N01	1	Dept 1
2	N01	2	Dept 1
3	N02	1	Dept 2
4	N02	2	Dept 2
5	N02	3	Dept 2
6	N02	4	Dept 3
7	N03	1	Dept 3
8	N04	1	Dept 5
9	N05	1	Dept 10
10	N05	2	Dept 10
11	N05	3	Dept 5
12	N06	1	Dept 6

How do I load only the records with the largest revision number, i.e. only the following records are loaded into a table ?

SN	REQ_NO	REV_NO	Department
2	N01	2	Dept 1
6	N02	4	Dept 3
7	N03	1	Dept 3
8	N04	1	Dept 5
11	N05	3	Dept 5
12	N06	1	Dept 6

[tamilarasu · Accepted solution]

Hi CheongWeei,

You can use inner join in this case.

Data:
LOAD * INLINE [
SN, REQ_NO, REV_NO, Department
1, N01, 1, Dept 1
2, N01, 2, Dept 1
3, N02, 1, Dept 2
4, N02, 2, Dept 2
5, N02, 3, Dept 2
6, N02, 4, Dept 3
7, N03, 1, Dept 3
8, N04, 1, Dept 5
9, N05, 1, Dept 10
10, N05, 2, Dept 10
11, N05, 3, Dept 5
12, N06, 1, Dept 6
];

Inner Join (Data)
Load  REQ_NO, Max(REV_NO) as REV_NO
Resident Data Group by REQ_NO;

Result:

[#3]

Nagaraj gave good example

[marjan_it]

you can use it in expression like this:

if(not IsNull(  aggr(max(REV_NO),REQ_NO)),aggr(max(REV_NO),REQ_NO))

[Anonymous]

Hi Nagaraj,

I just noted that my data has duplicate records . That is after loading only records with the largest revision number, there are still duplicate.

Sample of the original data

SN	REQ_NO	REV_NO	Department
1	N01	1	Dept 1
2	N01	2	Dept 1
3	N01	2	Dept 1
4	N02	1	Dept 2
5	N02	2	Dept 2
6	N03	1	Dept 2

After doing an inner join, I get this.

SN	REQ_NO	REV_NO	Department
2	N01	2	Dept 1
3	N01	2	Dept 1
5	N02	2	Dept 2
6	N03	1	Dept 2

What I would like to have is this

SN	REQ_NO	REV_NO	Department
2	N01	2	Dept 1
5	N02	2	Dept 2
6	N03	1	Dept 2

[tamilarasu]

Hello CheongWeei,

Add another inner join like below.

Data:
LOAD * INLINE [
SN, REQ_NO, REV_NO, Department
1, N01, 1, Dept 1
2, N01, 2, Dept 1
3, N01, 2, Dept 1
1, N01, 2, Dept 1
4, N02, 1, Dept 2
5, N02, 2, Dept 2
6, N03, 1, Dept 2
];

Inner Join (Data)
Load  REQ_NO, Max(REV_NO) as REV_NO
Resident Data Group by REQ_NO;

Inner Join (Data)
Load  REQ_NO, Min(SN) as SN
Resident Data Group by REQ_NO;

[Anonymous]

Nagaraj,

Got it ! Thanks

What if the records are exactly the same, ie, there is no SN field

REQ_NO, REV_NO, Department
N01, 1, Dept 1
N01, 2, Dept 1
N01, 2, Dept 1
N01, 2, Dept 1
N02, 1, Dept 2
N02, 2, Dept 2
N03, 1, Dept 2

[sunny_talwar]

Is this the required output?

Using this script:

Table:

LOAD REQ_NO,

  Max(REV_NO) as REV_NO,

  FirstSortedValue(DISTINCT Department, -REV_NO) as Department

Group By REQ_NO;

LOAD * Inline [

REQ_NO, REV_NO, Department

N01, 1, Dept 1

N01, 2, Dept 1

N01, 2, Dept 1

N01, 2, Dept 1

N02, 1, Dept 2

N02, 2, Dept 2

N03, 1, Dept 2

];

[tamilarasu]

CheongWeei,

You can use Sunny's solution or again inner join

Data:
Load Distinct * Inline [
REQ_NO, REV_NO, Department
N01, 1, Dept 1
N01, 2, Dept 1
N01, 2, Dept 1
N01, 2, Dept 1
N02, 1, Dept 2
N02, 2, Dept 2
N03, 1, Dept 2
];

Inner Join (Data)
Load  REQ_NO, Max(REV_NO) as REV_NO
Resident Data Group by REQ_NO;

Result:

[Anonymous]

Thanks !