SQLite Order Rows by Last Characters in a String

Question

I'm using an sqlite database for my Java application and I have a single varchar column with a bunch user stats that is written, read, and parsed by my Java program. What I want to do is have an query that can sort the rows by the last stat in the column. The stats are separated by commas and their lengths vary so I need something that can take the whole last section of the text (which is the text from the last comma to the end of the data) and order that. This would be easy to do just within my Java application but much more resource intensive which is why I would like to do this directly with the query. In practice the actual column data looks something like this:

2015/7/4 17:24:38,[(data1, 1, 1436394735787)|(data2, 4, 1436394739288)], 5

and I'm trying to order the rows based on that last 5 or whatever else it might be (it can be multiple digits too). Iv'e tried almost everything I could find on the internet but a lot of issues if had were because of syntax errors (even when I copied the query exactly) or problems where a specific function doesn't exist and i'm not really sure what the cause of those errors is. I'm not really familiar with MySQL so a simple answer would be the most appreciated.

This seems more like a database schema issue. You shouldn't have CSV in a column. — Hayley Guillou, Jul 09 '15 at 00:59
Also, MySQL and SQLite are not the same thing. SQLite supports a limited set of functions. — Hayley Guillou, Jul 09 '15 at 01:08
If you are using SQLite, there aren't enough functions to split by comma or take the last CSV numeric item. I'd recommend just using a programming language; or change the table structure as recommended by Hayley — zedfoxus, Jul 09 '15 at 01:24
@zedfoxus If i were to change the table structure what do you think way to structure it? I have to store a users votes: the time of the last vote, how many votes there are, the total number, and site of the vote. And i have to do that for all of the sites a user votes on, which isn't a static amount. — kmecpp, Jul 09 '15 at 01:29
Nice question...check out a somewhat normalized design that might help you: http://www.sqlfiddle.com/#!7/ba462/11 — zedfoxus, Jul 09 '15 at 02:00
If you prefer to denormalize but still make the table decently querable, you could do a single-table design like so: http://www.sqlfiddle.com/#!7/6d656/1 — zedfoxus, Jul 09 '15 at 02:03

score 3 · Accepted Answer · answered Jul 09 '15 at 06:14

3

As a quick and hacky solution (low performance if you have huge amount of data):

SELECT * FROM [tbl] ORDER BY CAST(SUBSTR([col], INSTR([col], '],') + 2) AS INTEGER);

But as Hayley suggested, to re-evaluate the data, you can use:

INSERT INTO [new-tbl]
SELECT SUBSTR(val, 0, c1),
       SUBSTR(val, c1+1, c2-c1),
       CAST(SUBSTR(val, c2+2) AS INTEGER),
       [more-cols]
FROM (
    SELECT INSTR([col], ',') AS c1,
           INSTR([col], '],') AS c2,
           [col] AS val,
           [more-cols]
    FROM [tbl]);

answered Jul 09 '15 at 06:14

John

1,856
2
22
33

Thanks for an actual solution! Ill probably rethink the database structure though. – kmecpp Jul 10 '15 at 14:07
I find this solution works just find with a result set of 1000 rows or less (the max I have tested against). The query completes with no noticeable delay to the user. – JJJones_3860 Sep 28 '22 at 16:53

score 1 · Answer 2 · edited May 23 '17 at 11:51

1

The solution to you problem is not the answer to your question. A delimited list of stats should not be a column in your table. See this question for more information. Instead, re-evaluate your schema and sort your query by the appropriate column using:

SELECT [columns to select] FROM tableName ORDER BY [column to order by]

edited May 23 '17 at 11:51

Community

1
1

answered Jul 09 '15 at 01:07

Hayley Guillou

3,953
4
24
34

There _is_ a way to do your query on the table as is using MySQL, but unfortunately SQLite is missing many of the built in functions you would need to do that. So your best long term bet is to normalize your schema. – Tim Biegeleisen Jul 09 '15 at 01:13
I think you have a different idea of what i'm trying to do. There is really only one stat that i'm collecting and that is a users votes. The user can vote at multiple different places so making a different column for every single place isn't really the best idea. I also have to store the date and time of the vote as well as how many there are, and its a lot easier just to do that all in one column and save the space as well. This column really just stores a string representation of java class that I can read to recreate an instance of it and writes the current one to when the user logs off. – kmecpp Jul 09 '15 at 01:15
If that was explained poorly... I have to store a users votes: the time of the last vote, how many votes there are, the total number, and site of the vote. And i have to do that for all of the sites a user votes on, which can changes whenever a user votes at a new site. – kmecpp Jul 09 '15 at 01:31
Having a table that strictly keeps track of votes sounds the most feasible for you. Each vote can have a userID, siteID, time, etc. Then you can query the total votes by userID, by siteID, etc. – Hayley Guillou Jul 09 '15 at 01:38
@kmecpp you really should try to structure your data correctly though, taking shortcuts is a sure way to lead to errors. – Hayley Guillou Jul 09 '15 at 01:40
@HayleyGuillou It's not like I'm trying to take shortcuts, the entire thing is handled by the Java app. It just seemed a little unnecessary at first to create a whole different table separate from the main userdata table just for such a minor voting feature. – kmecpp Jul 09 '15 at 01:51

score -1 · Answer 3 · answered Jul 09 '15 at 01:00

-1

SQL supports an ORDER BY function where you can order specific columns.

SELECT *last column name* FROM *table name* ORDER BY *last column name*

You can add DESC at the end for descending if you need.

answered Jul 09 '15 at 01:00

Teo

558
1
4
10

SQLite Order Rows by Last Characters in a String

3 Answers3