# Rank columns across rows

**URL:** <https://forum.kx.com/t/rank-columns-across-rows/11840>\
**Category:** Community Support\
**Tags:** kdb-and-q\
**Created:** [November 14, 2018, 11:22pm UTC](https://forum.kx.com/t/rank-columns-across-rows/11840 "2018-11-14T23:22:00Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Ben\_R](https://avatars.discourse-cdn.com/v4/letter/b/a698b9/32.png) [@Ben\_R](https://forum.kx.com/u/Ben_R)\
**Post date:** [November 14, 2018, 11:22pm UTC](https://forum.kx.com/t/rank-columns-across-rows/11840/1 "2018-11-14T23:22:00Z")

</div>

How can I generate a result that would ‘apply’ rank across various columns?&nbsp; Ex:

(a:09:30 11:00 10:00;b:14:00 09:00 15:00;c: 11:00 11:30 12:00)

col d=

0 2 1

1 0 2

0 2 1

and d will be either a string or even integers (no spaces) if easier.

Thx.

---

<div class="post-metadata">

**Author:** ![mtaggart](https://avatars.discourse-cdn.com/v4/letter/m/67e7ee/32.png) [@mtaggart](https://forum.kx.com/u/mtaggart)\
**Post date:** [November 15, 2018, 1:12pm UTC](https://forum.kx.com/t/rank-columns-across-rows/11840/2 "2018-11-15T13:12:00Z")

</div>

Hi Ben,

Are you looking for something like this?

q)t:(a:09:30 11:00 10:00;b:14:00 09:00 15:00;c: 11:00 11:30 12:00)

q)update d:iasc@/: value flip t from t

a b c d

-----------------------

09:30 14:00 11:00 0 2 1

11:00 09:00 11:30 1 0 2

10:00 15:00 12:00 0 1 2

Matthew.

---

<div class="post-metadata">

**Author:** ![cathal\_o\_neill](https://avatars.discourse-cdn.com/v4/letter/c/8c91f0/32.png) [@cathal\_o\_neill](https://forum.kx.com/u/cathal_o_neill)\
**Post date:** [November 15, 2018, 1:24pm UTC](https://forum.kx.com/t/rank-columns-across-rows/11840/3 "2018-11-15T13:24:00Z")

</div>

Hi Ben

rank each d should work&nbsp;

Cathal

---

<div class="post-metadata">

**Author:** ![mtaggart](https://avatars.discourse-cdn.com/v4/letter/m/67e7ee/32.png) [@mtaggart](https://forum.kx.com/u/mtaggart)\
**Post date:** [November 15, 2018, 1:38pm UTC](https://forum.kx.com/t/rank-columns-across-rows/11840/4 "2018-11-15T13:38:00Z")

</div>

Apologies, mine should read&nbsp;

q)update d:iasc@/: flip value flip t from t

---

<div class="post-metadata">

**Author:** ![cathal\_o\_neill](https://avatars.discourse-cdn.com/v4/letter/c/8c91f0/32.png) [@cathal\_o\_neill](https://forum.kx.com/u/cathal_o_neill)\
**Post date:** [November 15, 2018, 5:01pm UTC](https://forum.kx.com/t/rank-columns-across-rows/11840/5 "2018-11-15T17:01:00Z")

</div>

Hi Ben

I’m going to expand a little on my previous reply. The rank operator (or idesc/iasc) are what you are looking for. For example,

q)a: 5 1 2 4

q)rank a

3 0 1 2

or

q)iasc a

1 2 3 0

q)a iasc a

1 2 4 5

However, you want this applied row by row. If the column names were not an issue you could write this as:

q)update d:rank each t from t

a b c d

-----------------------

09:30 14:00 11:00 0 2 1

11:00 09:00 11:30 1 0 2

10:00 15:00 12:00 0 2 1

Note that here I have assigned the table as param&nbsp;t. However in all likelihood the column names may clash so you may be better doing this:

q)update d:rank each (a;b;c) from t

a b c d

-----------------------

09:30 14:00 11:00 0 2 1

11:00 09:00 11:30 1 0 2

10:00 15:00 12:00 0 1 2

To extend further this could be made into a functional select. To work out the structure of a functional select we use parse

q)parse “update d:rank each (a;b;c) from t”

!

`t

()

0b

(,`d)!,(k){x'y};k){$[0h&gt;@x;'`rank;\<\<x]};(enlist;`a;`b;`c))

q)![t;();0b;(enlist`d)!enlist (each;rank;(enlist;`a;`b;`c))]

a b c d

-----------------------

09:30 14:00 11:00 0 2 1

11:00 09:00 11:30 1 0 2

10:00 15:00 12:00 0 2 1

Finally,&nbsp;another alternative for a more readable function is&nbsp;

{update d:rank’[y#t] from t}

with the table as param t&nbsp;and columns to rank as y. I hope this is of some assistance.

Cathal

---

<div class="post-metadata">

**Author:** ![mtaggart](https://avatars.discourse-cdn.com/v4/letter/m/67e7ee/32.png) [@mtaggart](https://forum.kx.com/u/mtaggart)\
**Post date:** [November 15, 2018, 5:35pm UTC](https://forum.kx.com/t/rank-columns-across-rows/11840/6 "2018-11-15T17:35:00Z")

</div>

I’d be careful when using&nbsp;

rank each t

as it will apply rank to dictionaries and not the values themselves. This can be seen if you rename the columns

t:(b:09:30 11:00 10:00;a:14:00 09:00 15:00;c: 11:00 11:30 12:00)

q)update d:rank’[`b`a`c#t] from t

b a c d

-----------------------

09:30 14:00 11:00 2 0 1

11:00 09:00 11:30 0 1 2

10:00 15:00 12:00 2 0 1

We can see what happens as rank is just iasc applied twice

q)rank first t

2 0 1

q)first t

b| 09:30

a| 14:00

c| 11:00

q)iasc first t

`b`c`a

q)iasc `b`c`a

2 0 1

Below is an example of how you might apply it to the values

q)update d:rank@/: flip value flip [`b`a`c#t] from t

b a c d

-----------------------

09:30 14:00 11:00 0 2 1

11:00 09:00 11:30 1 0 2

10:00 15:00 12:00 0 2 1

I should probably say that my previous answer worked as a fluke due to there being only three columns.

---

<div class="post-metadata">

**Author:** ![Rolf1](https://avatars.discourse-cdn.com/v4/letter/r/7c8e57/32.png) [@Rolf1](https://forum.kx.com/u/Rolf1)\
**Post date:** [November 17, 2018, 6:12pm UTC](https://forum.kx.com/t/rank-columns-across-rows/11840/7 "2018-11-17T18:12:00Z")

</div>

q)f:{@[x;z;:;rank’[x[;y]]]}

q)f[t;`b`a`c;`r]

b&nbsp;&nbsp;&nbsp;&nbsp; a&nbsp;&nbsp;&nbsp;&nbsp; c&nbsp;&nbsp;&nbsp;&nbsp; r

* * *

09:30 14:00 11:00 0 2 1

11:00 09:00 11:30 1 0 2

10:00 15:00 12:00 0 2 1