Re: [sqlite] SQL query on sort order
If not already done creating a page of additional collations on the wiki would make sense and minimize work all around. > I just mailed you an extension for SQLite offering the collation you need. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL query on sort order
Harish, >We have a problem with a sql query. >In a table, a column called "name" contains character data that may >include >alpha, numeric and special characters. It is required to sort in such >a way >that names starting with alpha characters are listed first, then numerals >and finally special characters. >For example, the column contains data: [Bhaskar, 5th user, anand, ##, >Anand, >bhaskar ]. >When lsited, it should appear as [Anand, anand, Bhaskar, bhaskar, 5th >user, >##]. I just mailed you an extension for SQLite offering the collation you need. Hope it will fit your bill. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL query on sort order
>From the first post I've got the impression that only first character matters for you. When such sort order should persist over all characters you can't do it with simple query. Only the custom collation can help you. Pavel On Wed, Dec 15, 2010 at 7:29 AM, Harish CS wrote: > > Hello Pavel, > > Thanks. > The substr() compares the first character only. > For example, if the data is [CAT=$, CAT1$], it has to be sorted as > [CAT1$, CAT=$] because when '=' and '1' are compared, '1' has to come first. > Thanks for any suggestions. > -Harish > > > > Pavel Ivanov-2 wrote: >> >> If you want to do that completely in SQL without using collations you >> can do something like this: >> >> select name, >> case when substr(name, 1, 1) between 'A' and 'Z' or >> substr(name, 1, 1) between 'a' and 'z' >> then upper(name) >> when susbtr(name, 1, 1) between '0' and '9' then '|' || upper(name) >> else '~' || upper(name) >> end as sort_col >> from mytable >> order by sort_col; >> >> (I used the fact that '~' goes after '|' in ASCII) >> >> But of course you can achieve much better performance with collation >> or user-defined function implementing all this logic. >> >> >> Pavel >> >> On Tue, Dec 14, 2010 at 9:16 AM, Harish CS wrote: >>> >>> Hi, >>> We have a problem with a sql query. >>> In a table, a column called "name" contains character data that may >>> include >>> alpha, numeric and special characters. It is required to sort in such a >>> way >>> that names starting with alpha characters are listed first, then numerals >>> and finally special characters. >>> For example, the column contains data: [Bhaskar, 5th user, anand, ##, >>> Anand, >>> bhaskar ]. >>> When lsited, it should appear as [Anand, anand, Bhaskar, bhaskar, 5th >>> user, >>> ##]. >>> The query "select name from mytable order by name asc;" lists data in >>> this >>> order [##, 5th user, Anand, Bhaskar, anand, bhaskar], which is not >>> correct. >>> Any ideas on this? All thoughts are welcome. >>> -Thanks >>> Harish >>> -- >>> View this message in context: >>> http://old.nabble.com/SQL-query-on-sort-order-tp30455015p30455015.html >>> Sent from the SQLite mailing list archive at Nabble.com. >>> >>> ___ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> > > -- > View this message in context: > http://old.nabble.com/SQL-query-on-sort-order-tp30455015p30463371.html > Sent from the SQLite mailing list archive at Nabble.com. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL query on sort order
Harish CS wrote: > Collation is okay for case insensitivity but since here we have digits and > special chars I think it may not be helpful. Plz correct me if I am wrong. You can implement a custom collation that defines any order of your choice. See http://www.sqlite.org/c3ref/create_collation.html -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL query on sort order
Hello Pavel, Thanks. The substr() compares the first character only. For example, if the data is [CAT=$, CAT1$], it has to be sorted as [CAT1$, CAT=$] because when '=' and '1' are compared, '1' has to come first. Thanks for any suggestions. -Harish Pavel Ivanov-2 wrote: > > If you want to do that completely in SQL without using collations you > can do something like this: > > select name, > case when substr(name, 1, 1) between 'A' and 'Z' or > substr(name, 1, 1) between 'a' and 'z' > then upper(name) > when susbtr(name, 1, 1) between '0' and '9' then '|' || upper(name) > else '~' || upper(name) > end as sort_col > from mytable > order by sort_col; > > (I used the fact that '~' goes after '|' in ASCII) > > But of course you can achieve much better performance with collation > or user-defined function implementing all this logic. > > > Pavel > > On Tue, Dec 14, 2010 at 9:16 AM, Harish CS wrote: >> >> Hi, >> We have a problem with a sql query. >> In a table, a column called "name" contains character data that may >> include >> alpha, numeric and special characters. It is required to sort in such a >> way >> that names starting with alpha characters are listed first, then numerals >> and finally special characters. >> For example, the column contains data: [Bhaskar, 5th user, anand, ##, >> Anand, >> bhaskar ]. >> When lsited, it should appear as [Anand, anand, Bhaskar, bhaskar, 5th >> user, >> ##]. >> The query "select name from mytable order by name asc;" lists data in >> this >> order [##, 5th user, Anand, Bhaskar, anand, bhaskar], which is not >> correct. >> Any ideas on this? All thoughts are welcome. >> -Thanks >> Harish >> -- >> View this message in context: >> http://old.nabble.com/SQL-query-on-sort-order-tp30455015p30455015.html >> Sent from the SQLite mailing list archive at Nabble.com. >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://old.nabble.com/SQL-query-on-sort-order-tp30455015p30463371.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL query on sort order
Pavel Ivanov: Thank you very much. We used the query. -Harish Pavel Ivanov-2 wrote: > > If you want to do that completely in SQL without using collations you > can do something like this: > > select name, > case when substr(name, 1, 1) between 'A' and 'Z' or > substr(name, 1, 1) between 'a' and 'z' > then upper(name) > when susbtr(name, 1, 1) between '0' and '9' then '|' || upper(name) > else '~' || upper(name) > end as sort_col > from mytable > order by sort_col; > > (I used the fact that '~' goes after '|' in ASCII) > > But of course you can achieve much better performance with collation > or user-defined function implementing all this logic. > > > Pavel > > On Tue, Dec 14, 2010 at 9:16 AM, Harish CS wrote: >> >> Hi, >> We have a problem with a sql query. >> In a table, a column called "name" contains character data that may >> include >> alpha, numeric and special characters. It is required to sort in such a >> way >> that names starting with alpha characters are listed first, then numerals >> and finally special characters. >> For example, the column contains data: [Bhaskar, 5th user, anand, ##, >> Anand, >> bhaskar ]. >> When lsited, it should appear as [Anand, anand, Bhaskar, bhaskar, 5th >> user, >> ##]. >> The query "select name from mytable order by name asc;" lists data in >> this >> order [##, 5th user, Anand, Bhaskar, anand, bhaskar], which is not >> correct. >> Any ideas on this? All thoughts are welcome. >> -Thanks >> Harish >> -- >> View this message in context: >> http://old.nabble.com/SQL-query-on-sort-order-tp30455015p30455015.html >> Sent from the SQLite mailing list archive at Nabble.com. >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://old.nabble.com/SQL-query-on-sort-order-tp30455015p30462073.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL query on sort order
Kishor: Collation is okay for case insensitivity but since here we have digits and special chars I think it may not be helpful. Plz correct me if I am wrong. Puneet Kishor-2 wrote: > > > > Harish CS wrote: >> Hi, >> We have a problem with a sql query. >> In a table, a column called "name" contains character data that may >> include >> alpha, numeric and special characters. It is required to sort in such a >> way >> that names starting with alpha characters are listed first, then numerals >> and finally special characters. > > > Read up on collation on sqlite.org > > > >> For example, the column contains data: [Bhaskar, 5th user, anand, ##, >> Anand, >> bhaskar ]. >> When lsited, it should appear as [Anand, anand, Bhaskar, bhaskar, 5th >> user, >> ##]. >> The query "select name from mytable order by name asc;" lists data in >> this >> order [##, 5th user, Anand, Bhaskar, anand, bhaskar], which is not >> correct. >> Any ideas on this? All thoughts are welcome. >> -Thanks >> Harish > > > > -- > Puneet Kishor http://punkish.org > Carbon Model http://carbonmodel.org > Charter Member, Open Source Geospatial Foundation http://www.osgeo.org > Science Fellow > http://creativecommons.org/about/people/fellows#puneetkishor > Nelson Institute, UW-Madison http://www.nelson.wisc.edu > --- > Assertions are politics; backing up assertions with evidence is science > === > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://old.nabble.com/SQL-query-on-sort-order-tp30455015p30462063.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL query on sort order
If you want to do that completely in SQL without using collations you can do something like this: select name, case when substr(name, 1, 1) between 'A' and 'Z' or substr(name, 1, 1) between 'a' and 'z' then upper(name) when susbtr(name, 1, 1) between '0' and '9' then '|' || upper(name) else '~' || upper(name) end as sort_col from mytable order by sort_col; (I used the fact that '~' goes after '|' in ASCII) But of course you can achieve much better performance with collation or user-defined function implementing all this logic. Pavel On Tue, Dec 14, 2010 at 9:16 AM, Harish CS wrote: > > Hi, > We have a problem with a sql query. > In a table, a column called "name" contains character data that may include > alpha, numeric and special characters. It is required to sort in such a way > that names starting with alpha characters are listed first, then numerals > and finally special characters. > For example, the column contains data: [Bhaskar, 5th user, anand, ##, Anand, > bhaskar ]. > When lsited, it should appear as [Anand, anand, Bhaskar, bhaskar, 5th user, > ##]. > The query "select name from mytable order by name asc;" lists data in this > order [##, 5th user, Anand, Bhaskar, anand, bhaskar], which is not correct. > Any ideas on this? All thoughts are welcome. > -Thanks > Harish > -- > View this message in context: > http://old.nabble.com/SQL-query-on-sort-order-tp30455015p30455015.html > Sent from the SQLite mailing list archive at Nabble.com. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL query on sort order
Harish CS wrote: > Hi, > We have a problem with a sql query. > In a table, a column called "name" contains character data that may include > alpha, numeric and special characters. It is required to sort in such a way > that names starting with alpha characters are listed first, then numerals > and finally special characters. Read up on collation on sqlite.org > For example, the column contains data: [Bhaskar, 5th user, anand, ##, Anand, > bhaskar ]. > When lsited, it should appear as [Anand, anand, Bhaskar, bhaskar, 5th user, > ##]. > The query "select name from mytable order by name asc;" lists data in this > order [##, 5th user, Anand, Bhaskar, anand, bhaskar], which is not correct. > Any ideas on this? All thoughts are welcome. > -Thanks > Harish -- Puneet Kishor http://punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Fellow http://creativecommons.org/about/people/fellows#puneetkishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
