# Sorting or ordering by specific values

**URL:** <https://discuss.elastic.co/t/sorting-or-ordering-by-specific-values/67244>\
**Category:** Elasticsearch\
**Created:** [November 26, 2016, 4:25pm UTC](https://discuss.elastic.co/t/sorting-or-ordering-by-specific-values/67244 "2016-11-26T16:25:25Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![midget2000x](https://avatars.discourse-cdn.com/v4/letter/m/bbe5ce/32.png) [@midget2000x](https://discuss.elastic.co/u/midget2000x)\
**Post date:** [November 26, 2016, 4:25pm UTC](https://discuss.elastic.co/t/sorting-or-ordering-by-specific-values/67244/1 "2016-11-26T16:25:25Z")

</div>

I have a client that wants to show specific products first when a category is viewed (specifically I'm using a bool query for categories). Can this be accomplished with ES?

For mysql, you'd use the CASE statement like this:

```
ORDER BY CASE sku
	WHEN 'sku1' then 1 WHEN 'sku2' then 2 WHEN 'sku3' then 3 ELSE 4 END

```

Not sure if ES has an equivalent, or perhaps there's a completely different way to approach it.

---

<div class="post-metadata">

**Author:** ![midget2000x](https://avatars.discourse-cdn.com/v4/letter/m/bbe5ce/32.png) [@midget2000x](https://discuss.elastic.co/u/midget2000x)\
**Post date:** [November 26, 2016, 5:11pm UTC](https://discuss.elastic.co/t/sorting-or-ordering-by-specific-values/67244/2 "2016-11-26T17:11:27Z")

</div>

This query _seems_ like it should work, but returns "-1" for the first position of the sort on all results:

```
"sort": [
  -1,
  9678,
  8
]

```

the query (skuid\_text is indexed as a 'keyword' type, and I have verified that the result set includes the SKUs specified in sortOrder):

```
{
  "size" : "50",
	"_source": ["skuid", "name", "sort_priority","sales"],
  "sort" : [
		{
		  "_script": {
		  	"type": "number",
			"script": {
				"inline" : "params.sortOrder.indexOf(doc['skuid_text'].value)",
				"params": {
				  "sortOrder": [
					"CELCO2017L",
					"CELCO2017G",
					"S21024",
					"WED419",
					"WED420",
					"SOLS2622",
					"FDOR2124",
					"FDOR2125",
					"S21023",
					"CELCO2005L",
					"CELCO2005G",
					"S21022",
					"10171",
					"10172",
					"FDO4124P",
					"WED35",
					"WED37",
					"S2917",
					"CELCO2003L",
					"CELCO2003G",
					"S2769",
					"S2932",
					"S2936",
					"FADRQKE1006W",
					"WED306",
					"WED307",
					"CLAD30DE",
					"CELCO2004L",
					"CELCO2004G",
					"SOLS2466"
				  ]
				}			
			},
			"order": "asc"
		  }
		},
		{ "sort_priority" : {"order" : "asc"}},
		{ "sales" : "desc" }
  ],
	"query" :{
		"bool" : {
			"must" : [
				{
					"term" : {
						"category_codes" : "IRJB"
					}
				}
			]
		}
	},
   "aggs" : {
      "size_apparel" : {
         "terms" : {
            "field" : "size_apparel"
         }
      },
      "color" : {
         "terms" : {
            "field" : "color"
         }
      },
      "price" : {
         "range" : {
            "ranges" : [
               {
                  "to" : 20
               },
               {
                  "from" : 20,
                  "to" : 50
               },
               {
                  "to" : 100,
                  "from" : 50
               },
               {
                  "from" : 100,
                  "to" : 200
               },
               {
                  "from" : 200
               }
            ],
            "field" : "price"
         }
      },
      "category" : {
         "terms" : {
            "field" : "category"
         }
      },
      "birthstone" : {
         "terms" : {
            "field" : "birthstone"
         }
      },
      "size_jewelry" : {
         "terms" : {
            "field" : "size_jewelry"
         }
      },
      "recipient" : {
         "terms" : {
            "field" : "recipient"
         }
      },
      "occasion" : {
         "terms" : {
            "field" : "occasion"
         }
      }
   }
}

```

I have tried both doc['skuid\_text'].value and doc['skuid\_text']

---

<div class="post-metadata">

**Author:** ![midget2000x](https://avatars.discourse-cdn.com/v4/letter/m/bbe5ce/32.png) [@midget2000x](https://discuss.elastic.co/u/midget2000x)\
**Post date:** [November 26, 2016, 8:05pm UTC](https://discuss.elastic.co/t/sorting-or-ordering-by-specific-values/67244/3 "2016-11-26T20:05:12Z")

</div>

Actually the query DOES kind of work, it's just that the way I was ordering with "asc" was pushing all the skus in the sortOrder array to the end.

If you reverse the order of the skus in sortOrder and do some math on it, it will sort correctly. Kind of hack-y though. I'd love to know if there's a better way anyone can think of.

```
  {
    	"sort" : [
    		{
    		  "_script": {
    			"type": "number",
    			"script": {
    				"inline" : "(9999)-params.sortOrder.indexOf(doc['skuid_text'].value)",
    				"params": {
    				  "sortOrder": [
    					"SKUID3",
    					"SKUID2",
    					"SKUID1"
    				  ]
    				}			
    			},
    			"order": "asc"
    		  }
    		}
    	],
    	"query" :{
    		"bool" : {
    			"must" : [
    				{
    					"term" : {
    						"category_codes" : "CATEGORY1"
    					}
    				}
    			]
    		}
    	}
    }
```

---

<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:** [December 24, 2016, 8:05pm UTC](https://discuss.elastic.co/t/sorting-or-ordering-by-specific-values/67244/4 "2016-12-24T20:05:35Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.