Advertise here




Advertise here

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

sorting sqlite rows into appropriate table sections

WarblrWarblr Posts: 178Registered Users @ @
edited November 2013 in iOS SDK Development
Sadly I've been developing with obj-c for almost 4 years and still don't feel I have a handle on this ...

Scenario:
sqlite database with 3 columns: title, pub_year, copies_sold. I'd like users to be able to sort by pub_year or copies_sold and have the database rows displayed in the appropriate UITableView sections (i.e., a section for 1990, 1991, 1992, etc; or - 1 copy sold, 2 copies sold, etc). The issue I run into is the database has over 1000 rows already, and users can add more rows, so the information is not static. Due to the high number doing a "SELECT *" from the database and then sorting into appropriate sections with a 'for loop' takes a bit of time. I'm wondering if it is possible to have the sqlite database sort data into groups executing a SELECT command, as this would be much faster in an indexed database.

Any suggestions on how best to accomplish this would be greatly apprecaited.


Replies

  • khunterkhunter Posts: 44New Users @
    Within your sqlite database, add an index on pub_year and on copies_sold.

    Then, include an "order by" clause on your sql query - the results will come out in sorted order, so you don't have to sort them manually.

    e.g.:

    SELECT title, pub_year, copies_sold FROM tableName ORDER BY pub_year

    Now all you'd have to do is break the list into the groups.
  • WarblrWarblr Posts: 178Registered Users @ @
    Thanks, khunter! I was looking into GROUP BY yesterday here. I'm fairly new to SQL(ite), so I was not aware of this clause. That will definitely get things in the preferred order, but I'll still need to run the for loop in obj-c to get them separated into appropriate sections (i.e., a dictionary of arrays of rows per section). I suspect that breaking the list into groups is actually what is slowing things down, not the query from the database.
Sign In or Register to comment.