2

Duh right off the bat you'd think, "use ORDER BY and then the column" but I have values of:

  1. A
  2. Z
  3. B
  4. a
  5. z

And when I sort them using this query:

SELECT * FROM Diaries ORDER BY title ASC;

I then get this:

  1. A
  2. B
  3. Z
  4. a
  5. z

When I want to get something like this, first issue:

  1. A
  2. a
  3. B
  4. Z
  5. z

I had the same sorting issue else where, second issue, but I was able to fix it with this: By temporarily putting all characters in lowercase

for (NSString *key in [dicGroupedStories allKeys]) {
    [dicGroupedStories setValue: [[dicGroupedStories objectForKey: key] sortedArrayUsingComparator:^NSComparisonResult(id a, id b) {
        NSString *stringA = [[a objectStory_title] lowercaseString];
        NSString *stringB = [[b objectStory_title] lowercaseString];

        return [stringA compare: stringB];
    }] forKey: key];

}

Only reason why I don't use this Comparator to sort my first issue is bc I don't want to execute my query then sort them then use the array.

Question: I want to know if there's a way to sort them how I want, like I did in my second issue, in a SQL query

objects id a and id b are arrays that contain other objects like title, date created, description, etc. objectDiary_title returns a NSString

RandomSeed
  • 29,301
  • 6
  • 52
  • 87
ErickES7
  • 558
  • 6
  • 16
  • 1
    Can you add a `LOWER(field) as sortField` to the select, and in the order by do it like `ORDER BY sortField, title;` That should order by lowercase first then by uppercase so you get basically what you want. – MiltoxBeyond Jul 20 '15 at 22:48
  • Dang, this was quick Thanks! I'll give it a try soon, so I would have to add this to the select like `SELECT .. TOLOWER(title) as sortField .. from Diaries .. ORDER BY sortField, title;` ? – ErickES7 Jul 20 '15 at 22:52
  • 1
    Exactly how do you want case sorted? You could apply lower case to everything in the `ORDER BY` clause, but that might give something like `A,A,a,a,A,a,B...` where you're actually wanting `A,A,A,a,a,a,B,b,b...` –  Jul 20 '15 at 22:54
  • I noticed that lowercase letters come second to capital letters, like A,B,Z,a,z vs the A,a,B,Z,z – ErickES7 Jul 20 '15 at 22:59

3 Answers3

5

In SQL, you can use the lower() or upper() functions in the order by:

ORDER BY lower(Diaries), diaries
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

You can use COLLATE with xxx_ci where ci means case insensitive. For example:

SELECT * FROM Diaries ORDER BY title COLLATE 'latin1_general_ci' ASC;

There's more information regarding case sensitivity in MySQL here: https://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html. It's useful for doing searches and comparisons as well.

mongjong
  • 479
  • 3
  • 6
  • Whoa, is it necessary to have such a specific sensitivity? I just want to mix the capital letters and the lower case so you won't find a's at the bottom of the list bc it's lowercase – ErickES7 Jul 20 '15 at 23:36
  • It's really up to you and what you need done. If the lower() method works for you then that's fine as well. I was just giving you another option :) – mongjong Jul 20 '15 at 23:43
  • Thank you! :) might come to using this method when I have more examples of titles of diary – ErickES7 Jul 20 '15 at 23:52
  • Sadly the collation is returning, `Error: no such collation sequence: latin1_general_ci` Is this suppose to be working on MySQL? – ErickES7 Jul 21 '15 at 01:06
  • It depends on the encoding that you are using in your database and, more specifically, for that column. You can try running SHOW FULL COLUMNS FROM Diaries; This will give you the details of the collation. You then use what it states there for your title column, e.g. if it says 'latin1_swedish' then use 'latin1_swedish_ci'. As far as I am aware latin1_swedish is the default so chances are 'latin1_swedish_ci' will work. – mongjong Jul 21 '15 at 01:18
  • Alrighty cool, I'll try that and check it out. Not to familiar with SQL. Kinda just looked at it and began learning so idk any database types blah blah :) – ErickES7 Jul 21 '15 at 01:54
  • Sadly i am not getting anything from running `SHOW FULL COLUMNS FROM Diaries;` so i went with just `lower( title )` – ErickES7 Jul 21 '15 at 23:22
1

Use a case-insensitive collation, such as:

ORDER BY Diaries COLLATE utf8_unicode_ci ;

However, changing collation on-the-fly, like any convertion on-the-fly, makes the query unable to use an index (which is acceptable if the data set to be sorted is small enough).

If performance is an issue then you had better reindex the column with the target collation:

ALTER TABLE MODIFY COLUMN Diaries VARCHAR(10) COLLATE utf8_unicode_ci ;

ORDR BY will then be case insensitive by defaut and can use an index on this column.

utf8_unicode_ci is just an example. Just make sure you use a collation *_ci (for Case-Insensitive) which is compatible with the column's encoding

Community
  • 1
  • 1
RandomSeed
  • 29,301
  • 6
  • 52
  • 87