Re: [sqlite] Exceptions to sorting?
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
Re: [sqlite] Exceptions to sorting?
On Sat, May 17, 2008 at 09:30:30AM +0200, Petite Abeille scratched on the wall: > > On May 16, 2008, at 11:07 PM, Igor Tandetnik wrote: > > > Well, for any string A there exists another string B that sorts > > after A. > > How can I guarantee that, after I choose A as my "sorts after > > everything" marker, somebody doesn't put B into the database? > > Well... not to beat a dead horse or anything, but... if one is worried > about the entire range of Unicode data points... one can always use > the highest collation data point as a marker... And if the highest data point is a "z" (for example), someone could put the string "zz" into the DB that will sort after it. If you use "zz" as the marker, they could put "zzz" in the DB. And so on. The only thing that breaks "for any string A there exists another string B that sorts after A" is the maximum string length. In SQLite that's rather large-- especially to be using as a constant. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "'People who live in bamboo houses should not throw pandas.' Jesus said that." - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Exceptions to sorting?
On May 16, 2008, at 11:07 PM, Igor Tandetnik wrote: > Well, for any string A there exists another string B that sorts > after A. > How can I guarantee that, after I choose A as my "sorts after > everything" marker, somebody doesn't put B into the database? Well... not to beat a dead horse or anything, but... if one is worried about the entire range of Unicode data points... one can always use the highest collation data point as a marker... on the other hand, this is rather academic in practice... "Don't Let Architecture Astronauts Scare You" http://www.joelonsoftware.com/articles/fog18.html -- PA. http://alt.textdrive.com/nanoki/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Exceptions to sorting?
Petite Abeille <[EMAIL PROTECTED]>
wrote:
> On May 16, 2008, at 10:41 PM, Igor Tandetnik wrote:
>
>> That would sort 'zebra' after ''.
>
> Well... this is meant as an example... 'z' should be whatever
> character one deems appropriate, e.g. '{' or whatever utf-8 sequence
> does the job.
Well, for any string A there exists another string B that sorts after A.
How can I guarantee that, after I choose A as my "sorts after
everything" marker, somebody doesn't put B into the database?
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Exceptions to sorting?
"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
Re: [sqlite] Exceptions to sorting?
On May 16, 2008, at 10:40 PM, Andrés G. Aragoneses wrote: > Interesting, but the replacement to 'z' seems kind of a hack, I would > not prefer magic strings... Well... it doesn't have to be 'z'... it's just an example... choose whatever character sequence is relevant to get the proper ordering for a given collation... nothing magic... rather lexicographic :) -- PA. http://alt.textdrive.com/nanoki/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Exceptions to sorting?
On May 16, 2008, at 10:41 PM, Igor Tandetnik wrote:
> That would sort 'zebra' after ''.
Well... this is meant as an example... 'z' should be whatever
character one deems appropriate, e.g. '{' or whatever utf-8 sequence
does the job.
--
PA.
http://alt.textdrive.com/nanoki/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Exceptions to sorting?
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?
Thanks again,
Andres
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Exceptions to sorting?
Petite Abeille <[EMAIL PROTECTED]> wrote: > On May 16, 2008, at 10:23 PM, Andrés G. Aragoneses wrote: > >> If I use "ORDER BY ItemName = '', ItemNameLowered" I get 2,1,3 and I >> want to get 1,3,2. Any ideas? > > Perhaps something like: > > select * > from item > order by case >when name = '' then 'z' > else name > end That would sort 'zebra' after ''. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Exceptions to sorting?
Petite Abeille wrote: > On May 16, 2008, at 10:23 PM, Andrés G. Aragoneses wrote: > >> If I use "ORDER BY ItemName = '', ItemNameLowered" I get 2,1,3 and I >> want to get 1,3,2. Any ideas? > > Perhaps something like: > > select * > from item > order by case > when name = '' then 'z' > else name >end > Interesting, but the replacement to 'z' seems kind of a hack, I would not prefer magic strings... Anyway thanks for your idea! Andres -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Exceptions to sorting?
"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;
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Exceptions to sorting?
On May 16, 2008, at 10:23 PM, Andrés G. Aragoneses wrote: > If I use "ORDER BY ItemName = '', ItemNameLowered" I get 2,1,3 and I > want to get 1,3,2. Any ideas? Perhaps something like: select * from item order by case when name = '' then 'z' else name end -- PA. http://alt.textdrive.com/nanoki/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Exceptions to sorting?
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?
Regards,
Andres
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Exceptions to sorting?
"Andrés G. Aragoneses" <[EMAIL PROTECTED]>
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! I've also noticed that every non-alphabetical
> character is also ordered before alphabetical ones, how can I invert
> this behaviour?
You would have to implement a custom collation. See
sqlite3_create_collation[16]
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Exceptions to sorting?
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! I've also noticed that every non-alphabetical character
is also ordered before alphabetical ones, how can I invert this behaviour?
Regards,
Andres
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
