About FirstSortedValue

[balanandam]

Hi,

I have data as below

sample:

LOAD * INLINE

[ NO, name, status

1,'Bala',5

2,'Bala',4

3,'Bala',5

4,'Suresh',5

5,'Suresh',4

6,'Suresh',5

];

I need out put as follows

NO

3

6

I used below code:

final:

LOAD FirstSortedValue( DISTINCT NO,-status) AS NO Group by name;

LOAD * Resident sample order by NO desc;

is this the best way ? or suggest if any other that this ?

Regards,

Balanandam

[vishsaggi]

You cant use DISTINCT in your firstsortedvalue. Try like below:

Max:

LOAD * INLINE

[ NO, name, status

1,'Bala',5

2,'Bala',4

3,'Bala',5

4,'Suresh',5

5,'Suresh',4

6,'Suresh',5

];

INNER JOIN

LOAD name,

     FirstSortedValue(NO, -NO) AS NO,

     Max(NO)                   AS MaxNum

Resident Max

Group By name;

[sunny_talwar]

Since you have two NO with the same max status... which of the two would you like to pick?

[effinty2112]

Hi,

Try

name	Max(Aggr(If(status = max(TOTAL<name> status),NO),name,NO))
Bala	3
Suresh	6

This will return the highest value of NO if there is a tie.

Cheers

Andrew

[trdandamudi]

One more possible way to do this:

Source_Data:
LOAD * INLINE
[ NO,Name, Status
1,'Bala',5
2,'Bala',4
3,'Bala',5
4,'Suresh',5
5,'Suresh',4
6,'Suresh',5
];

NoConcatenate
Temp:
Load Max(NO) as Number
Resident Source_Data
Group by Name;

NoConcatenate
Final:
Load NO, Name, Status
Resident Source_Data
Where exists(Number,NO);


Drop Table Source_Data,Temp;

[balanandam]

I need highest status(possible values: 1,2,3,4,5) at Max NO.

[sunny_talwar]

You want to pick status at highest NO? May be this

LOAD Max(NO) as MaxNo,

     name,

     FirstSortedValue(status, -NO) as MaxNoStatus

Resident ...

Group By name;