Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Since you have two NO with the same max status... which of the two would you like to pick?
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
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;
I need highest status(possible values: 1,2,3,4,5) at Max NO.
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;