Sqlite and Custom Order By

SQLite and custom order by

ORDER BY 
  CASE ID
    WHEN 4 THEN 0
    WHEN 3 THEN 1
    WHEN 1 THEN 2
    WHEN 5 THEN 3
    WHEN 6 THEN 4
  END

Custom ordering in sqlite

  SELECT _id, Name, Key 
    FROM my_table t 
ORDER BY CASE WHEN key = 'Key' THEN 0 
              WHEN key = 'Named' THEN 1 
              WHEN key = 'Contributing' THEN 2 END, id;

How to write a custom sort using SQLite

You can use MAX() window function in the ORDER BY clause to get the max DateCode of each part and sort by that descending:

SELECT *
FROM Part
ORDER BY MAX(DateCode) OVER (PARTITION BY Part) DESC,
         Part, -- just in case 2 different parts have the same max DateCode
         DateCode DESC;

See the demo.

Query Sqlite Database by specific/custom ordering?

EDIT:

Tested, works:

SELECT * FROM books WHERE _id IN(4, 1, 5) 
ORDER BY
CASE _id
WHEN '4' THEN 1
WHEN '1' THEN 2
WHEN '5' THEN 3
ELSE 4
END, _id;

Sqlite custom order by of [character]-[number]

You must sort the table first by the string part of the id before the dash and then by the number after the dash.

So you need string functions to extract the string and the numeric part of the column:

select * from mytable
order by 
  substr(id, 1, instr(id, '-')),
  substr(id, instr(id, '-') + 1) + 0

The arithmetic operator + will enforce an implicit conversion of the numeric part of the string to a number.

See the demo.

If the pattern of the ids is exactly like your sample data and there is always 1 letter at the start, followed by a dash and then a number, then the code can be simplified:

select * from mytable
order by
  substr(id, 1, 1),
  substr(id, 3) + 0 

See the demo.

Results:

| id   |
| ---- |
| B-1  |
| B-2  |
| B-10 |
| B-11 |

Reorder all rows in a SQLite table with a custom `order_index` column

This requirement is tricky because it involves multiple updates which depend on each other and the order of these updates is not guaranteed.

First update all the other rows that must be updated except the row of the person that you want to move:

with cte as (
  select name, order_index current_index, ? new_index
  from persons 
  where name = 'Muhammed'
) 
update persons
set order_index = order_index + 
  case 
    when (select current_index from cte) > (select new_index from cte) then 1 
    else -1 
  end
where name <> (select name from cte) 
  and order_index between 
    min((select current_index from cte), (select new_index from cte))
    and
    max((select current_index from cte), (select new_index from cte));

Then update the row of the person that you want to move:

update persons
set order_index = ?                                        
where name = 'Muhammed'; 

Replace ? placeholders in both queries with the new position.

See the demo.

SQLite ORDER BY Without Special Ordering

You can use CASE in your ORDER BY clause to specify custom ordering:

SELECT Car  
FROM AntiqueCars  
ORDER BY CASE WHEN Color = 'red' THEN 1 WHEN Color = 'green' THEN 2 ELSE 3 END

---

Related Topics