Igor Tandetnik wrote:
> "Andrés G. Aragoneses" <[EMAIL PROTECTED]>
> wrote:
>> Igor Tandetnik wrote:
>>> "Andrés G. Aragoneses"
>>> <[EMAIL PROTECTED]> wrote:
>>>> Andrés G. Aragoneses wrote:
>>>>> Igor Tandetnik wrote:
>>>>>> "Andrés G. Aragoneses"
>>>>>> <[EMAIL PROTECTED]> wrote:
>>>>>>> Hello, I am querying a list of elements and some of them are
>>>>>>> blank (''). However, I want that those who are blank appear as
>>>>>>> the last (instead of the first) elements when I sort with ORDER
>>>>>>> BY alphabetically. How can I do this with just SQL?
>>>>>> ORDER BY text = '', text
>>>>> It works thanks!
>>>> Hugh, but this seems not to be applicable for different columns,
>>>> because, let's suppose I have:
>>>>
>>>> ItemId ItemName ItemNameSort
>>>> 1 'Balloon' 'balloon'
>>>> 2 '' '[noname]'
>>>> 3 'Car' 'car'
>>>>
>>>> If I use "ORDER BY ItemName = '', ItemNameLowered" I get 2,1,3 and I
>>>> want to get 1,3,2. Any ideas?
>>> What's ItemNameLowered? Your table doesn't seem to have such a
>>> column.
>>>
>>> Have you tested it? I don't see how row 2 could appear first. You
>>> don't actually have apostrophes stored in the database as part of
>>> your data, do you? What does the following statement output:
>>>
>>> select ItemId, ItemName, length(ItemName)
>>> from myTable;
>> Sorry, a typo: not ItemNameLowered but ItemNameSort. When I use '' is
>> to differentiate it from the int type (which I used for the key). So
>> let's rewrite the question:
>>
>> ItemId ItemName ItemNameSort
>> 1 'Balloon' 'balloon'
>> 2 '' '[noname]'
>> 3 'Car' 'car'
>>
>> If I use "ORDER BY ItemName = '', ItemNameSort ASC" I get 2,1,3 and I
>> want to get 1,3,2. Any ideas?
>
> I tested it, and I get 1, 3, 2. I can reproduce your result only if I
> put NULL into ItemName, rather than an empty string. What does this
> statement return:
You're right, I had nulls instead of empty strings.
> select ItemId, ItemName, typeof(ItemName)
> from myTable
>
> If you do need to handle NULLs, simply change the condition to
>
> ORDER BY ifnull(ItemName, '')='', ItemNameSort ASC
>
Thanks!
Andres
--
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
