0

I am sure I am just having a brain freeze and this one is bonehead simple, but in SQLite when I execute the following query it appears to be ordering the results:

SELECT col_Referncia FROM lookup_57c5f58b0c4543df97fe5929498aa2bf

enter image description here

When I do the wild card on the same table, it does not ( I am only showing that column again, but there are other columns).

SELECT * FROM lookup_57c5f58b0c4543df97fe5929498aa2bf

enter image description here

The way the code is written I must specify the column. Is there a way to have it return in a non-sorted fashion (like I see with wildcard)?

The table does have several columns and indexes. I suspect this has something to do with the indexes?

Stephen McCormick
  • 1,706
  • 22
  • 38

1 Answers1

2

No, this is not possible. You should always specify which order you want it to be set. That said, in the first image, you specified a column, so SQL will default it to ASC ordering by the columns values, while in the second image it's probably ordering by a primary key or the first column values (you don't fully show the table schema, so we can't know), since you selected all columns.

Either way, you should never rely on the DB to order the items for you.

Assuming your PK is id you can just simply do this:

SELECT col_Referncia FROM lookup_57c5f58b0c4543df97fe5929498aa2bf ORDER BY id ASC

But still this will not guarantee that the ordering will always be like you see there.

Another solution is to update your col_Referncia values.

Right now your columns are being order like this:

1 - xxxxxxx
10 - xxxxxxx
100 - xxxxxxx
2 - xxxxxxx

What I suggest you to do is to update your entries so the numbers have leading 0's eg:

000001 - xxxxxxx
000002 - xxxxxxx
000010 - xxxxxxx
000100 - xxxxxxx

This way the ordering will be correct.

Henrique B.
  • 450
  • 3
  • 9
  • That makes a lot of sense. My problem is that this data is actually generated by the user and we dynamically generate the table and columns based on the data they provide. They then want the data back exactly as it was entered (they use a CSV file to specify the data) into the DB table. The columns are not set and can vary greatly. Does that make sense? In other words they want any query to return the results in insertion order. Ugh – Stephen McCormick Feb 17 '17 at 00:09
  • 1
    A solution for that would could be to add a column `created_at` that will automatically add the current timestamp, and you can just order by it. Look here http://stackoverflow.com/questions/200309/sqlite-database-default-time-value-now on how to create a timestamp column. – Henrique B. Feb 17 '17 at 00:13
  • Right. I was thinking I could also tack on an auto incrementing number as the table is being built. Unfortunately there are thousands of these unique tables out there...this will work on a go forward basis, but for existing tables (unless the data gets updated) will remain incorrect. – Stephen McCormick Feb 17 '17 at 00:24