Custom Order by to Ignore 'The'

Custom ORDER BY to ignore 'the'

Best is to have a computed column to do this, so that you can index the computed column and order by that. Otherwise, the sort will be a lot of work.

So then you can have your computed column as:

CASE WHEN title LIKE 'The %' THEN stuff(title,1,4,'') + ', The' ELSE title END

Edit: If STUFF isn't available in MySQL, then use RIGHT or SUBSTRING to remove the leading 4 characters. But still try to use a computed column if possible, so that indexing can be better. The same logic should be applicable to rip out "A " and "An ".

Rob

Sort ordering in Django: possible to ignore The prefix when sorting?

You can't do it with order_by()

You can

1. Do some fancy SQL. If you want to keep it nice and short, you should write a custom model manager.
2. Do sorting at Python-level, not SQL-level
3. Store "sort title" and "display title" separately.

How to ignore 'the' when sorting WordPress categories alphabetically

Way 1: Using slugs as orderby parameter:

You can do it with small trick.

1. remove "the" from taxonomy slugs.(strings which appear at URL)

2. Change orderby parameter from name to slug.

          <?php wp_dropdown_categories(array( 
               'hide_empty' => 0, 
               'orderby' =>'slug', 
               'name' => 'category', 
               'hierarchical' => true, 
               'taxonomy' => EM_TAXONOMY_CATEGORY, 
               'selected' => $args['category'], 
               'show_option_none' => $args['categories_label'], 
               'option_none_value'=> 0, 
               'class'=>'em-events-search-category'
           ));
   

Way 2: Setting custom orderby:

  function custom_meta_order($dropdown) {
  $dropdown['orderby'] = "ORDER BY REPLACE(t.name,'The ','')";
  return $dropdown;
}
add_filter('terms_clauses','custom_meta_order');

MySQL - Ordering alphabetically, excluding The

You can do this with a SQL query, by using a conditional statement in the order by:

order by (case when keyword like 'The %' then substr(keyword, 5, 100)
               else keyword
          end)

How to exclude some values in sorting with MySQL?

You could try something like:

ORDER BY IF(SORT=0, 999999999, SORT)

How to ignore - and . characters in a value during sort comparison?

Why don't you make a custom sort function liek this:

var x = ['12.5', '11.3', '13-5', '10-0'];
x.sort(function(a, b){
    a = a.replace(/[-]/, '.');
    b = b.replace(/[-]/, '.');
    if( parseInt(a) < parseInt(b) ) return -1;
    if( parseInt(a) > parseInt(b) ) return 1;
    return 0;
});

Output:

["10-0", "11.3", "12.5", "13-5"]

This will also work if you have 125.5 and so on. because the . and the - are both used in the compare.

Example with >= 100

So input:

["100-0", "11.3", "12.5", "13-5"]

Will output

["11.3", "12.5", "13-5", "100-0"]

Ignore The and A when sorting a listview in C#

What you could do is to create a customer comparer and set it on your ListView instance using the ListView.ListViewItemSorter property. Then, your comparer is responsible for remvoing "the" and "a" from the start of the items being compared.

When your ListView is sorted, it will use this custom comparer to sort with, but the original values including "the" and "a" will be used as display values in the ListView (ie. you do not need to modify the values you put in the ListView - the comparer just ignores the words you want it to when sorting).

MySQL Orderby a number, Nulls last

MySQL has an undocumented syntax to sort nulls last. Place a minus sign (-) before the column name and switch the ASC to DESC:

SELECT * FROM tablename WHERE visible=1 ORDER BY -position DESC, id DESC

It is essentially the inverse of position DESC placing the NULL values last but otherwise the same as position ASC.

A good reference is here http://troels.arvin.dk/db/rdbms#select-order_by

---

Related Topics