"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:
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
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
