Top 10 Records of Each Department

[zain16nib]

Hi,

Given below statement is working fine when I talking about top 10

First 10

Load * from abc.csv

order by sal desc;

My question is how I do to get Top 10 sal by each department (in script level only), suppose if I have 4 departments so I will get 40 records means top 10 records for each department.

Regards,

Zain.

[techvarun]

Get Top N Records from a Table based on Group by

[johanlindell]

Hi,

You can't do order on file loads. But you can when the file has been loaded.

This should work:

abc_tmp:

Load * from abc.csv;

abc:

NoConcatenate

Load * Resident abc_tmp

order by sal desc;

DROP Table abc_tmp;

This might also work:

abc:

First 10

Load *

order by sal desc;

Load * from abc.csv;

[zain16nib]

Thanks, Johan for your reply,

I know very well which you mentioned in your solution,

I want the top n group by department mean top n of each department.

Regards,

Zain.

[johanlindell]

Hi, sorry for not reading prooperly, you need to do this in two steps: 1 rank the records and 2, filter out the ranked lines. It should go something like this (not tested)

abc_tmp:

Load * from abc.csv;

abc_tmp2:

NoConcatenate

Load *,

         If (Department = Peek (Department),

             RangeSum (1, Peek (Rank)),

             1) as Rank

Resident abc_tmp

Order By Department, sal desc;

DROP Table abc_tmp;

abc:

NoConcatenate

LOAD *

Resident abc_tmp2

Where Rank <= 10;

DROP Table abc_tmp2;

That should do it.

[zain16nib]

thanks, I will check on Monday and then give you feedback on this regard.

Zain.