Get record holds the highest number

[renjithpl]

Hi All,

I have a table like this

ID	RandomNumber	Country
1	152	Australia
1	162	Canada

While loading i want

ID	RandomNumber	Country
1	162	Canada

to get only one record for an ID with highest RandomNumber. My output should look like

    Any ideas.?

[swuehl]

LOAD ID,

         Max(RandomNumber) as RandomNumber,

         FirstSortedValue(Country, -RandomNumber)

FROM ...

GROUP BY ID;

or

Table:

LOAD ID, RandomNumber, Country

FROM ....;

INNER JOIN

LOAD ID, Max(RandomNumber) as RandomNumber

RESIDENT Table

GROUP BY ID;

[renjithpl]

I am getting both the records

[renjithpl]

The second one is working, let me verify. THanks much.

[#5]

LOAD *

SQL select

ID ,RandomNumber, Country

from table where RandomNumber=(select max(RandomNumber) from table) ;

try the above

[swuehl · Accepted solution]

Both should work.

Working samples:

Table:
LOAD ID, Max(RandomNumber) as RandomNumber, FirstSortedValue(Country, -RandomNumber) as Country
 INLINE [
ID, RandomNumber, Country
1, 152, Australia
1, 162, Canada
]
GROUP BY ID;

or

Table:
LOAD * INLINE [
ID, RandomNumber, Country
1, 152, Australia
1, 162, Canada
];


INNER JOIN
LOAD ID, Max(RandomNumber) as RandomNumber
Resident Table
GROUP BY ID;