# Specific sorting order using windowfunction row\_number

**URL:** <https://discuss.elastic.co/t/specific-sorting-order-using-windowfunction-row-number/37893>\
**Category:** Elasticsearch\
**Created:** [December 23, 2015, 10:09pm UTC](https://discuss.elastic.co/t/specific-sorting-order-using-windowfunction-row-number/37893 "2015-12-23T22:09:10Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![krisgeus](https://avatars.discourse-cdn.com/v4/letter/k/45deac/32.png) [@krisgeus](https://discuss.elastic.co/u/krisgeus)\
**Post date:** [December 23, 2015, 10:09pm UTC](https://discuss.elastic.co/t/specific-sorting-order-using-windowfunction-row-number/37893/1 "2015-12-23T22:09:10Z")

</div>

I have a very specific requirement in sorting the results from my elastic index.  
I want to order by a date (new collection) but I want to distribute the results in such a way that  
not all documents with the same brand end up in the firstt page of results because they are added the most recent  
For this I think I need some kind of rank over partitioning function (row\_number window function in SQL).

I hope to clarify this requirement with this small example

I want to be able to execute the following two steps during query execution:

step 1:

rank over brand order by date  
{"id": 1, "brand": "sony", "sortingdate": "04-12-2015"} =\> rank 1  
{"id": 2, "brand": "sony", "sortingdate": "03-12-2015"} =\> rank 2  
{"id": 3, "brand": "sony", "sortingdate": "02-12-2015"} =\> rank 3  
{"id": 4, "brand": "phillips", "sortingdate": "06-12-2015"} =\> rank 1  
{"id": 5, "brand": "phillips", "sortingdate": "02-12-2015"} =\> rank 2  
{"id": 6, "brand": "phillips", "sortingdate": "01-12-2015"} =\> rank 3 or 4  
{"id": 7, "brand": "phillips", "sortingdate": "01-12-2015"} =\> rank 3 or 4  
{"id": 8, "brand": "samsung", "sortingdate": "04-12-2015"} =\> rank 1  
{"id": 9, "brand": "samsung", "sortingdate": "01-12-2015"} =\> rank 2

step 2:

order by ranknumber, brand, date  
{"id": 4, "brand": "phillips", "sortingdate": "06-12-2015"} =\> rank 1  
{"id": 8, "brand": "samsung", "sortingdate": "04-12-2015"} =\> rank 1  
{"id": 1, "brand": "sony", "sortingdate": "04-12-2015"} =\> rank 1  
{"id": 5, "brand": "phillips", "sortingdate": "02-12-2015"} =\> rank 2  
{"id": 9, "brand": "samsung", "sortingdate": "01-12-2015"} =\> rank 2  
{"id": 2, "brand": "sony", "sortingdate": "03-12-2015"} =\> rank 2  
{"id": 6, "brand": "phillips", "sortingdate": "01-12-2015"} =\> rank 3 or 4  
{"id": 3, "brand": "sony", "sortingdate": "02-12-2015"} =\> rank 3  
{"id": 7, "brand": "phillips", "sortingdate": "01-12-2015"} =\> rank 3 or 4

Is this currently possible using existing functionality in elastic or is it possible to add this through a plugin or otherwise.

Thanks in advance

Kris

---

<div class="post-metadata">

**Author:** ![D3s7r0y3r](https://avatars.discourse-cdn.com/v4/letter/d/7993a0/32.png) [@D3s7r0y3r](https://discuss.elastic.co/u/D3s7r0y3r)\
**Post date:** [May 22, 2017, 1:39pm UTC](https://discuss.elastic.co/t/specific-sorting-order-using-windowfunction-row-number/37893/2 "2017-05-22T13:39:16Z")

</div>

Hi, were you able to come at a solution? As I find myself in a similar situation.

---

<div class="post-metadata">

**Author:** ![krisgeus](https://avatars.discourse-cdn.com/v4/letter/k/45deac/32.png) [@krisgeus](https://discuss.elastic.co/u/krisgeus)\
**Post date:** [May 23, 2017, 12:27pm UTC](https://discuss.elastic.co/t/specific-sorting-order-using-windowfunction-row-number/37893/3 "2017-05-23T12:27:41Z")

</div>

No solution. Not working on the problem anymore also. Still interested if a solution is available though

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 5, 2017, 10:00pm UTC](https://discuss.elastic.co/t/specific-sorting-order-using-windowfunction-row-number/37893/4 "2017-07-05T22:00:10Z")

</div>