9

I have a database in SQLlite and I'd like to sort my table in an alphabetical order. How can I do it? Is there a way to sort entries using only SQLite or do I have first to read a table into an array, sort it and afterwards to write it into a database?

Here is my query: "SELECT entry FROM table WHERE id=?" I need to get data from the table using this statement in order to get only one entry at a time.

Thank you in advance, Ilya.

T .
  • 4,874
  • 3
  • 23
  • 36
Ilya Suzdalnitski
  • 52,598
  • 51
  • 134
  • 168

3 Answers3

16
'Select name from table order by name asc'

'asc' is ascending, will give you the text field in alphabetical order, conversely 'desc' will give it to you in reverse alphabetical order.

Edit: as a general rule, you should let the database do the sorting. The below post is related, and arguably, almost the same. You might find it helpful:

PHP/SQL: ORDER BY or sort($array)?

Community
  • 1
  • 1
karim79
  • 339,989
  • 67
  • 413
  • 406
  • Thank you, this helps. But how can I assign new IDs to each row after sorting? So that changes will appear in my table. If I want to get an entry with an ID=0, it will give me a word starting from A and so on. – Ilya Suzdalnitski Mar 21 '09 at 20:55
  • @Ilya: ID keys are not row numbers. They need to be unique, but they are not in any order (nor contiguous). Sort based on the `name` column, and use the ID value only as a way to reference the row. – Bill Karwin Mar 21 '09 at 20:59
  • Do you mean you want to re-order the data alphabetically such that the the first (alphabetical) entry is assigned the lowest id number? You can insert into a new table, and then delete the old one, e.g. SELECT name INTO table2 FROM table order by name asc – karim79 Mar 21 '09 at 21:04
  • 2
    watch out for case sensitive sorting with names. I'd prefer to use 'order by upper(name) asc' – Martlark Mar 21 '09 at 23:31
2

It is also worth emphasising that the order of data in any SQL database table, or retrieved from such using a query that does not include a order by clause, is not defined.

In practice a straight read of a table without an order by will retrieve data in some fixed order and it's often the order of insert. However to rely on this is always an error, although one that is seen alarmingly often.

Cruachan
  • 15,733
  • 5
  • 59
  • 112
  • Thanks and I see this advice often repeated. Could you possibly list some examples of when assumptions made about insertion order could fail? For instance, joining another table would very likely change the row order, but then I see people tell themselves _so long as I don't join I can rely on the insert order_. So what else can break this unsafe assumption? Running sqlite on different platforms? Case (in)sensitive filesystems? Varying the page cache? (these are all just guesses) – ecoe Jun 27 '22 at 15:01
  • Another way relying on insert order can break is if the optimizer chooses a different way to output rows based on what Indexes it has available (which will all likely have different ordering), as shown here: https://stackoverflow.com/a/56096942/1080804 – ecoe Jun 27 '22 at 18:10
0

If you want to sort table without case sensitivity or with specific locale (not english) you have to add collation to your sqlite. Here is example how to do it.

Community
  • 1
  • 1
klew
  • 14,837
  • 7
  • 47
  • 59