Order by the in Value List

ORDER BY the IN value list

You can do it quite easily with (introduced in PostgreSQL 8.2) VALUES (), ().

Syntax will be like this:

select c.*
from comments c
join (
  values
    (1,1),
    (3,2),
    (2,3),
    (4,4)
) as x (id, ordering) on c.id = x.id
order by x.ordering

How to Sort a ListT by a property in the object

The easiest way I can think of is to use Linq:

List<Order> SortedList = objListOrder.OrderBy(o=>o.OrderDate).ToList();

sql ORDER BY multiple values in specific order?

...
WHERE
   x_field IN ('f', 'p', 'i', 'a') ...
ORDER BY
   CASE x_field
      WHEN 'f' THEN 1
      WHEN 'p' THEN 2
      WHEN 'i' THEN 3
      WHEN 'a' THEN 4
      ELSE 5 --needed only is no IN clause above. eg when = 'b'
   END, id

MySQL - ORDER BY values within IN()

SELECT id, name
FROM mytable
WHERE name IN ('B', 'A', 'D', 'E', 'C')
ORDER BY FIELD(name, 'B', 'A', 'D', 'E', 'C')

The FIELD function returns the position of the first string in the remaining list of strings.

However, it is much better performance-wise to have an indexed column that represents your sort order, and then sort by this column.

Get order of list by value

A way to get this index list, using only jdk5

    List<Float> positions = new ArrayList<Float>();
    List<Integer> indexes = new ArrayList<Integer>();
    positions.add(0.1f);
    positions.add(0.8f);
    positions.add(0.3f);

    // copy float elements to another List, so to keep the order
    List<Float> sortedPositions = new ArrayList<Float>();
    Collections.copy(sortedPositions, positions);
    Collections.sort(sortedPositions);

    for (Float position : positions) {
        indexes.add(sortedPositions.indexOf(position));
    }

    for (Integer index : indexes) {
        System.out.print(index + ",");
    }
    // prints 1,3,2,

sorting by a custom list in pandas

Below is an example that performs lexicographic sort on a dataframe.
The idea is to create an numerical index based on the specific sort.
Then to perform a numerical sort based on the index.
A column is added to the dataframe to do so, and is then removed.

import pandas as pd

# Create DataFrame
df = pd.DataFrame(
{'id':[2967, 5335, 13950, 6141, 6169],
    'Player': ['Cedric Hunter', 'Maurice Baker',
               'Ratko Varda' ,'Ryan Bowen' ,'Adrian Caldwell'],
    'Year': [1991, 2004, 2001, 2009, 1997],
    'Age': [27, 25, 22, 34, 31],
    'Tm': ['CHH' ,'VAN' ,'TOT' ,'OKC', 'DAL'],
    'G': [6, 7, 60, 52, 81]})

# Define the sorter
sorter = ['TOT', 'ATL', 'BOS', 'BRK', 'CHA', 'CHH', 'CHI', 'CLE', 'DAL','DEN',
          'DET', 'GSW', 'HOU', 'IND', 'LAC', 'LAL', 'MEM', 'MIA', 'MIL',
          'MIN', 'NJN', 'NOH', 'NOK', 'NOP', 'NYK', 'OKC', 'ORL', 'PHI',
          'PHO', 'POR', 'SAC', 'SAS', 'SEA', 'TOR', 'UTA', 'VAN',
          'WAS', 'WSB']

# Create the dictionary that defines the order for sorting
sorterIndex = dict(zip(sorter, range(len(sorter))))

# Generate a rank column that will be used to sort
# the dataframe numerically
df['Tm_Rank'] = df['Tm'].map(sorterIndex)

# Here is the result asked with the lexicographic sort
# Result may be hard to analyze, so a second sorting is
# proposed next
## NOTE: 
## Newer versions of pandas use 'sort_values' instead of 'sort'
df.sort_values(['Player', 'Year', 'Tm_Rank'],
        ascending = [True, True, True], inplace = True)
df.drop('Tm_Rank', 1, inplace = True)
print(df)

# Here is an example where 'Tm' is sorted first, that will 
# give the first row of the DataFrame df to contain TOT as 'Tm'
df['Tm_Rank'] = df['Tm'].map(sorterIndex)
## NOTE: 
## Newer versions of pandas use 'sort_values' instead of 'sort'
df.sort_values(['Tm_Rank', 'Player', 'Year'],
        ascending = [True , True, True], inplace = True)
df.drop('Tm_Rank', 1, inplace = True)
print(df)

Ordering by the order of values in a SQL IN() clause

Use MySQL's FIELD() function:

SELECT name, description, ...
FROM ...
WHERE id IN([ids, any order])
ORDER BY FIELD(id, [ids in order])

FIELD() will return the index of the first parameter that is equal to the first parameter (other than the first parameter itself).

FIELD('a', 'a', 'b', 'c')

will return 1

FIELD('a', 'c', 'b', 'a')

will return 3

This will do exactly what you want if you paste the ids into the IN() clause and the FIELD() function in the same order.

Order items in MySQL by a fixed list?

You can do that by using either:

ORDER BY FIND_IN_SET(id, '3,11,7,1')

or

ORDER BY FIELD(id, 3, 11, 7, 1)

or

ORDER BY CASE id WHEN 3 THEN 0
                WHEN 11 THEN 1
                 WHEN 7 THEN 2
                 WHEN 1 THEN 3
                        ELSE 4
         END

Order a list of objects by value match in another list

Use FindIndex:

list2.OrderBy(x => list1.FindIndex(y => y.prop1 == x.prop2));

Note that if no item is found that's matching the predicate, you will get -1 back, so all these items will be piled up at the top (of course, you can write code to ignore them completely, or pile them down at the bottom, or whatever.

ORDER BY the IN value list position

You are looking for custom sorting in elasticsearch

it is possible to achieve it via painless script

here is what I do

PUT my_test
{
  "mappings": {
    "properties": {
      "animal": {
        "type": "keyword"
      }
    }
  }
}

Populate docs

POST my_test/_doc
{
  "animal": "mouse"
}
POST my_test/_doc
{
  "animal": "cat"
}
POST my_test/_doc
{
  "animal": "dog"
}

Custom sort

GET my_test/_search
{
  "query": {
    "match_all": {}
  },

    "sort": {
        "_script": {
            "type": "number",
            "script": {
                "lang": "painless",
                "source": "if(params.scores.containsKey(doc['animal'].value)) { return params.scores[doc['animal'].value];} return 100000;",
                "params": {
                    "scores": {
                        "dog": 0,
                        "cat": 1,
                        "mouse": 2
                    }
                }
            },
            "order": "asc"
        }
    }
}

---

Related Topics