Top rank per group

Find the top N rankers per group.

Haskell

import Data.List
import Control.Monad
import Control.Arrow
import Text.Printf

groupingOn f a b = f a == f b
comparing f a b = compare (f a) (f b)
comparingDwn f a b = compare (f b) (f a)


type ID = Int
type DEP = [Char]
type NAME = [Char]
type SALARY = Double
type Employee = (ID, DEP, NAME, SALARY)

employees :: [Employee]
employees = [(1001,"AB","Janssen A.H.",41000), (101,"KA","'t Woud B.",45000),
             (1013,"AB","de Bont C.A.",65000), (1101,"CC","Modaal A.M.J.",30000),
             (1203,"AB","Anders H.",50000),    (100,"KA","Ezelbips P.J.",52000),
             (1102,"CC","Zagt A.",33000),     (1103,"CC","Ternood T.R.",21000),
             (1104,"CC","Lageln M.",23000),   (1105,"CC","Amperwat A.",19000),
             (1106,"CC","Boon T.J.",25000), (1107,"CC","Beloop L.O.",31000),
             (1009,"CD","Janszoon A.",38665), (1026,"CD","Janszen H.P.",41000),
             (1011,"CC","de Goeij J.",39000), (106,"KA","Pragtweik J.M.V.",42300),
             (111,"KA","Bakeuro S.",31000),  (105,"KA","Clubdrager C.",39800),
             (104,"KA","Karendijk F.",23000), (107,"KA","Centjes R.M.",34000),
             (119,"KA","Tegenstroom H.L.",39000), (1111,"CD","Telmans R.M.",55500),
             (1093,"AB","de Slegte S.",46987), (1199,"CC","Uitlaat G.A.S.",44500)
            ]

nr :: Employee -> ID
nr   (i,_,_,_) = i

dep :: Employee -> DEP
dep  (_,d,_,_) = d

name :: Employee -> NAME
name (_,_,n,_) = n

sal :: Employee -> SALARY
sal  (_,_,_,s) = s

dorank :: Int ->
          (Employee -> DEP) ->
          (Employee -> SALARY) ->
          [Employee]-> [[Employee]]
dorank n o1 o2 = map (take n. sortBy (comparingDwn o2))
                 . groupBy (groupingOn o1) . sortBy (comparing o1)

toprank :: IO ()
toprank = do
   printf "%-16s %3s %10s\n" "NAME" "DEP" "TIP" 
   printf "%s\n" $ replicate 31 '='
   mapM_ (mapM_ (ap (ap (printf "%-16s %3s %10.2g\n" . name) dep) sal)) $ dorank 3 dep sal employees

Output: top 3 per department

*Main> toprank
NAME             DEP        TIP
===============================
de Bont C.A.      AB   65000.00
Anders H.         AB   50000.00
de Slegte S.      AB   46987.00
Uitlaat G.A.S.    CC   44500.00
de Goeij J.       CC   39000.00
Zagt A.           CC   33000.00
Telmans R.M.      CD   55500.00
Janszen H.P.      CD   41000.00
Janszoon A.       CD   38665.00
Ezelbips P.J.     KA   52000.00
't Woud B.        KA   45000.00
Pragtweik J.M.V.  KA   42300.00

J

J has a rich set of primitive functions, which combine the power of an imperative language with the expressiveness of a declarative, SQL-like language:

   NB.  Dynamically generate convenience functions
   ('`',,;:^:_1: N=:{.Employees) =:, (_&{"1)`'' ([^:(_ -: ])L:0)"0 _~ i.# E =: {: Employees

   NB.  Show top ranked employees in each dept
   N , (<@:>"1@:|:@:((6 <. #) {. ] \: SALARY)/.~ DEPT) |: <"1&> E
+-----+-----+-----------------+------+
|ID   |DEPT |NAME             |SALARY|
+-----+-----+-----------------+------+
|1013 |AB   |de Bont C.A.     |65000 |
|1203 |AB   |Anders H.        |50000 |
|1093 |AB   |de Slegte S.     |46987 |
|1001 |AB   |Janssen A.H.     |41000 |
+-----+-----+-----------------+------+
|100  |KA   |Ezelbips P.J.    |52000 |
|101  |KA   |'t Woud B.       |45000 |
|106  |KA   |Pragtweik J.M.V. |42300 |
|105  |KA   |Clubdrager C.    |39800 |
|119  |KA   |Tegenstroom H.L. |39000 |
|107  |KA   |Centjes R.M.     |34000 |
+-----+-----+-----------------+------+
|1199 |CC   |Uitlaat G.A.S.   |44500 |
|1011 |CC   |de Goeij J.      |39000 |
|1102 |CC   |Zagt A.          |33000 |
|1107 |CC   |Beloop L.O.      |31000 |
|1101 |CC   |Modaal A.M.J.    |30000 |
|1106 |CC   |Boon T.J.        |25000 |
+-----+-----+-----------------+------+
|1111 |CD   |Telmans R.M.     |55500 |
|1026 |CD   |Janszen H.P.     |41000 |
|1009 |CD   |Janszoon A.      |38665 |
+-----+-----+-----------------+------+

using the data set:

   Employees=: (<;.1~(1 1{.~#);+./@:(;:E.S:0])@:{.)];._2 noun define
   ID   DEPT NAME             SALARY
   1001 AB   Janssen A.H.     41000 
   101  KA   't Woud B.       45000 
   1013 AB   de Bont C.A.     65000 
   1101 CC   Modaal A.M.J.    30000 
   1203 AB   Anders H.        50000 
   100  KA   Ezelbips P.J.    52000 
   1102 CC   Zagt A.          33000 
   1103 CC   Ternood T.R.     21000 
   1104 CC   Lageln M.        23000 
   1105 CC   Amperwat A.      19000 
   1106 CC   Boon T.J.        25000 
   1107 CC   Beloop L.O.      31000 
   1009 CD   Janszoon A.      38665 
   1026 CD   Janszen H.P.     41000 
   1011 CC   de Goeij J.      39000 
   106  KA   Pragtweik J.M.V. 42300 
   111  KA   Bakeuro S.       31000 
   105  KA   Clubdrager C.    39800 
   104  KA   Karendijk F.     23000 
   107  KA   Centjes R.M.     34000 
   119  KA   Tegenstroom H.L. 39000 
   1111 CD   Telmans R.M.     55500 
   1093 AB   de Slegte S.     46987 
   1199 CC   Uitlaat G.A.S.   44500 
   )

SMEQL

The following SMEQL example returns the top 6 earners in each department based on this table schema:

 table: Employees
 ----------------
 empID
 dept
 empName
 salary

Source Code:

 srt = orderBy(Employees, (dept, salary), order)
 top = group(srt, [(dept) dept2, max(order) order])
 join(srt, top, a.dept=b.dept2 and b.order - a.order < 6)