Task

Find the top   N   salaries in each department,   where   N   is provided as a parameter.

Use this data as a formatted internal data structure (adapt it to your language-native idioms, rather than parse at runtime), or identify your external data source:

Employee Name,Employee ID,Salary,Department
Tyler Bennett,E10297,32000,D101
John Rappl,E21437,47000,D050
George Woltman,E00127,53500,D101
Adam Smith,E63535,18000,D202
Claire Buckman,E39876,27800,D202
David McClellan,E04242,41500,D101
Rich Holcomb,E01234,49500,D202
Nathan Adams,E41298,21900,D050
Richard Potter,E43128,15900,D101
David Motsinger,E27002,19250,D202
Tim Sampair,E03033,27000,D101
Kim Arlich,E10001,57000,D190
Timothy Grove,E16398,29900,D190



use AppleScript version "2.3.1" -- Mac OS X 10.9 (Mavericks) or later for these 'use' commands!
use sorter : script "Custom Iterative Ternary Merge Sort" -- <https://macscripter.net/viewtopic.php?pid=194430#p194430>
 
on topNSalariesPerDepartment(employeeRecords, n)
    set output to {}
    set employeeCount to (count employeeRecords)
    if ((employeeCount > 0) and (n > 0)) then
        -- Sort a copy of the employee record list by department with descending subsorts on salary.
        copy employeeRecords to employeeRecords
        script comparer
            on isGreater(a, b)
                return ((a's department > b's department) or ((a's department = b's department) and (a's salary < b's salary)))
            end isGreater
        end script
        considering numeric strings
            tell sorter to sort(employeeRecords, 1, employeeCount, {comparer:comparer})
        end considering
 
        -- Initialise the output with data from the first record in the sorted list, then work through the rest of the list.
        set {department:previousDepartment, salary:previousSalary} to beginning of employeeRecords
        set {padValue, topSalaries, counter} to {missing value, {previousSalary}, 1}
        set end of output to {department:previousDepartment, salaries:topSalaries}
        repeat with i from 2 to employeeCount
            set {department:thisDepartment, salary:thisSalary} to item i of employeeRecords
            if (thisDepartment is previousDepartment) then
                -- Another record from the same department. Include its salary in the output if different from
                -- the previous one's and if fewer than n salaries from the department have been included so far.
                if ((thisSalary < previousSalary) and (counter < n)) then
                    set end of topSalaries to thisSalary
                    set counter to counter + 1
                    set previousSalary to thisSalary
                end if
            else
                -- First record of the next department.
                -- Pad the previous department's salary list with missing values if it has fewer than n entries.
                repeat (n - counter) times
                    set end of topSalaries to padValue
                end repeat
                -- Start a result record for the new department and add it to the output.
                set topSalaries to {thisSalary}
                set counter to 1
                set end of output to {department:thisDepartment, salaries:topSalaries}
                set previousDepartment to thisDepartment
                set previousSalary to thisSalary
            end if
        end repeat
        -- Pad the last department's salary list if necessary.
        repeat (n - counter) times
            set end of topSalaries to padValue
        end repeat
    end if
 
    return output
end topNSalariesPerDepartment
 
on demo()
    set employeeRecords to {¬
        {|name|:"Tyler Bennett", |ID|:"E10297", salary:32000, department:"D101"}, ¬
        {|name|:"John Rappl", |ID|:"E21437", salary:47000, department:"D050"}, ¬
        {|name|:"George Woltman", |ID|:"E00127", salary:53500, department:"D101"}, ¬
        {|name|:"Adam Smith", |ID|:"E63535", salary:18000, department:"D202"}, ¬
        {|name|:"Claire Buckman", |ID|:"E39876", salary:27800, department:"D202"}, ¬
        {|name|:"David McClellan", |ID|:"E04242", salary:41500, department:"D101"}, ¬
        {|name|:"Rich Holcomb", |ID|:"E01234", salary:49500, department:"D202"}, ¬
        {|name|:"Nathan Adams", |ID|:"E41298", salary:21900, department:"D050"}, ¬
        {|name|:"Richard Potter", |ID|:"E43128", salary:15900, department:"D101"}, ¬
        {|name|:"David Motsinger", |ID|:"E27002", salary:19250, department:"D202"}, ¬
        {|name|:"Tim Sampair", |ID|:"E03033", salary:27000, department:"D101"}, ¬
        {|name|:"Kim Arlich", |ID|:"E10001", salary:57000, department:"D190"}, ¬
        {|name|:"Timothy Grove", |ID|:"E16398", salary:29900, department:"D190"}, ¬
        {|name|:"Simila Pey", |ID|:"E16399", salary:29900, department:"D190"} ¬
            }
    set n to 4
    set topSalaryRecords to topNSalariesPerDepartment(employeeRecords, n)
    --> eg. {{department:"D050", salaries:{47000, 21900, missing value, missing value}}, … }
 
    -- Derive a text report from the result.
    set report to {"Top " & n & " salaries per department:"}
    set astid to AppleScript's text item delimiters
    set AppleScript's text item delimiters to "  "
    repeat with thisRecord in topSalaryRecords
        set i to n
        repeat while (item i of thisRecord's salaries is missing value)
            set item i of thisRecord's salaries to "-"
            set i to i - 1
        end repeat
        set end of report to thisRecord's department & ":    " & thisRecord's salaries
    end repeat
    set AppleScript's text item delimiters to linefeed
    set report to report as text
    set AppleScript's text item delimiters to astid
    return report
end demo
 
demo()