Re: [sqlite] URGENT - ORDER BY , COLLATE combination clarification required
Dear Richard, Thanks a lot for the quick reply. Please find the reply for your queries * Which models of Philips MP3 players are using SQLite? All the Hard disc audio juke box (MP3-HDD player)models of Philips for the year 2004 and 2005 are using the Sqlite database. * What other OSS is used in Philips MP3 players? I am a developer working in the design centre in Bangalore and I do not have the list of OSS other than Sqlite that is used in the creation of the Philips MP3 players. * When is Philips going to begin supporting Ogg Vorbis? I am not sure when Ogg Vorbis support shall be available in the Philips MP3 players. Currently we have only MP3/WMA support. * Where is your design team located? The software design team is located in Bangalore and I am a member of the software development group. Regards, Sankara Narayanan B "D. Richard Hipp" <[EMAIL PROTECTED]> 2004-12-07 06:53 PM Please respond to [EMAIL PROTECTED] To [EMAIL PROTECTED] cc Subject Re: [sqlite] URGENT - ORDER BY , COLLATE combination clarification required [EMAIL PROTECTED] wrote: > > CLARIFICATION REQUIRED: > If I have a non-text column (say integer column like iDirId) at the end in > the order by clause I find that collate nocase is not performed. Is this > the expected behaviour? If so how could I have the nocase collation for > the cPlaylistName even though I want the ordering sequence as above in > Query 2? > The COLLATE clause applies to the term of the ORDER BY to which it is attached. If you put the COLLATE on the iDirId, it works on that column. If you want it to nocase the cPlaylistName, attach it to the cPlaylistName column. Now I have questions for you: * Which models of Philips MP3 players are using SQLite? * What other OSS is used in Philips MP3 players? * When is Philips going to begin supporting Ogg Vorbis? * Where is your design team located? -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
[sqlite] Whole word searches
How do I search on whole words within a text field. For example, if I want to find the whole word "main" and don't want records with "maintain", how can I do that with Sqlite? I tried using brackets as specified in the SQL spec but I get errors. Here is some documentation from MS SQL Server: [ ] Any single character within the specified range ([a-f]) or set ([abcdef]). WHERE au_lname LIKE '[C-P]arsen' finds author last names ending with arsen and beginning with any single character between C and P, for example Carsen, Larsen, Karsen, and so on. This message was sent using IMP, the Internet Messaging Program.
Re: [sqlite] Implementing aggregate user functions
Of course, avg() is already in the spec, so this isn't the 'perfect' instance, You can easily maintain an average as you see each row at the cost of more divisions. (eg for the 9th item add one ninth of the difference between the item and the running average to the average). Calculating a median value is something you can't do to the end (or more accurately I can't think of a way of doing any work until you have all the values). Roger
Re: [sqlite] Setting The Width Option
On Tuesday 07 December 2004 10:28 am, Jeff Flowers wrote: > Is it possible to set the .width option when calling the sqlite > frontend? I was surprised that there wasn't a options to do this, > something like: > > sqlite3 -column -header -width '30 30 5 5' dbname > > You didn't mention your OS, but I know on Linux you can set whatever defaults you want in .sqliterc and the commandline utility uses them. Not sure if this is also true on the Win platform tho. Scott -- POPFile, the OpenSource EMail Classifier http://popfile.sourceforge.net/ Linux 2.6.5-7.111.5-default x86_64
[sqlite] Setting The Width Option
Is it possible to set the .width option when calling the sqlite frontend? I was surprised that there wasn't a options to do this, something like: sqlite3 -column -header -width '30 30 5 5' dbname Thanks, -- Jeff Flowers
Re: [sqlite] Inner selects
D. Richard Hipp wrote: [snipped...] SELECT * FROM table_1, (SELECT count(*) AS cnt, fk_id FROM table_2 GROUP BY fk_id) WHERE efk_owner='boba' AND fk_id=id; Coupled with a quick left join to return rows with empty counts that's solved my problem. Thanks, -- Bob Arnott
Re: [sqlite] Inner selects
Bob Arnott wrote: Hello, I've got a query about how to get round a limitation of SQLite (version 2.8.6) and inner selects. It's my understanding from what I've read on the website and what I've tried on the command line that I can't do: SELECT * FROM TABLE_1, (SELECT COUNT(1) FROM TABLE_2 WHERE TABLE_2.FK_ID=TABLE_1.ID) WHERE EFK_OWNER='boba'; As I can't reference the outer select from the inner one. Is there another way of getting a count of things from another table and appending it to a general select...? SELECT * FROM table_1, (SELECT count(*) AS cnt, fk_id FROM table_2 GROUP BY fk_id) WHERE efk_owner='boba' AND fk_id=id; -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
[sqlite] Inner selects
Hello, I've got a query about how to get round a limitation of SQLite (version 2.8.6) and inner selects. It's my understanding from what I've read on the website and what I've tried on the command line that I can't do: SELECT * FROM TABLE_1, (SELECT COUNT(1) FROM TABLE_2 WHERE TABLE_2.FK_ID=TABLE_1.ID) WHERE EFK_OWNER='boba'; As I can't reference the outer select from the inner one. Is there another way of getting a count of things from another table and appending it to a general select...? Thanks, -- Bob Arnott
Re: [sqlite] SQL "IS" operator. Was: field=null vs. field isnull
Dennis Cote wrote: > > The case you mentioned as a motivation can be handled using the > following expression. > > case > when :param is null then x is null > else x = :param > end > I though I should also mention a somewhat simpler equivalent expression that can be used to replace the proposed IS operator expresion with a parameter x is :param=>(:param is null and x is null) or x = :param This expresion can also be used anywhere the IS operator expresion can, and is clearer than the case expression I suggested earlier.
Re: [sqlite] Display Of Numbers
On Tue, 07 Dec 2004 08:58:41 -0600, "Kurt Welgehausen" <[EMAIL PROTECTED]> said: > > ... is there a way to make the second zero appear? > > You can replace "select dollarcolumn ..." in your queries with > "select round(dollarcolumn, 2) dollarcolumn ...". Thank you Kurt. That did the trick. Sincerely, Jeff Flowers
Re: [sqlite] Display Of Numbers
> ... is there a way to make the second zero appear? You can replace "select dollarcolumn ..." in your queries with "select round(dollarcolumn, 2) dollarcolumn ...". Regards
Re: [sqlite] URGENT - ORDER BY , COLLATE combination clarification required
[EMAIL PROTECTED] wrote: Hi, I need urgent clarification on the following behaviour with the following queries. I have a table called playlistTable as follows. CREATE TABLE playlistTable( iPlaylistId INTEGER PRIMARY KEY,cPlaylistName VARCHAR(100), cFileName VARCHAR(255),iDirId INTEGER ); CREATE INDEX playlist_cPlaylistName ON playlistTable (cPlaylistName); I have two queries as examples and the output as follows. QUERY 1: select * from playlistTable order by iDirId, cPlaylistName collate nocase 30 APlayList30 NO FILE 0 29 bPlayList29 NO FILE 0 27 ccPlayList27NO FILE 0 26 cPlayList26 NO FILE 0 28 CPlayList28 NO FILE 0 Query 2 select * from playlistTable order by cPlaylistName,iDirId collate nocase 30 APlayList30 NO FILE 0 28 CPlayList28 NO FILE 0 25 DPlayList25 NO FILE 0 1 PlayList1 NO FILE 0 In 2, only iDirId is sorted nocase. Try select * from playlistTable order by cPlaylistName collate nocase,iDirId Gerry -- -- Gerry Snyder AIS Director, Affiliates Chair, Symposium Chair in warm, winterless Los Angeles -- USDA zone 9b, Sunset 18-19
Re: [sqlite] URGENT - ORDER BY , COLLATE combination clarification required
[EMAIL PROTECTED] wrote: CLARIFICATION REQUIRED: If I have a non-text column (say integer column like iDirId) at the end in the order by clause I find that collate nocase is not performed. Is this the expected behaviour? If so how could I have the nocase collation for the cPlaylistName even though I want the ordering sequence as above in Query 2? The COLLATE clause applies to the term of the ORDER BY to which it is attached. If you put the COLLATE on the iDirId, it works on that column. If you want it to nocase the cPlaylistName, attach it to the cPlaylistName column. Now I have questions for you: * Which models of Philips MP3 players are using SQLite? * What other OSS is used in Philips MP3 players? * When is Philips going to begin supporting Ogg Vorbis? * Where is your design team located? -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
[sqlite] URGENT - ORDER BY , COLLATE combination clarification required
Hi, I need urgent clarification on the following behaviour with the following queries. I have a table called playlistTable as follows. CREATE TABLE playlistTable( iPlaylistId INTEGER PRIMARY KEY,cPlaylistName VARCHAR(100), cFileName VARCHAR(255),iDirId INTEGER ); CREATE INDEX playlist_cPlaylistName ON playlistTable (cPlaylistName); I have two queries as examples and the output as follows. QUERY 1: select * from playlistTable order by iDirId, cPlaylistName collate nocase 30 APlayList30 NO FILE 0 29 bPlayList29 NO FILE 0 27 ccPlayList27NO FILE 0 26 cPlayList26 NO FILE 0 28 CPlayList28 NO FILE 0 25 DPlayList25 NO FILE 0 3 myPlaylist3 NO FILE 0 1 PlayList1 NO FILE 0 11 PlayList11 NO FILE 0 12 PlayList12 NO FILE 0 13 PlayList13 NO FILE 0 14 PlayList14 NO FILE 0 15 PlayList15 NO FILE 0 16 PlayList16 NO FILE 0 17 PlayList17 NO FILE 0 18 PlayList18 NO FILE 0 19 PlayList19 NO FILE 0 2 playList2 NO FILE 0 20 PlayList20 NO FILE 0 21 PlayList21 NO FILE 0 22 PlayList22 NO FILE 0 23 PlayList23 NO FILE 0 24 PlayList24 NO FILE 0 4 PlayList4 NO FILE 0 5 PlayList5 NO FILE 0 6 PlayList6 NO FILE 0 7 PlayList7 NO FILE 0 8 yourplaylist8 NO FILE 0 9 YourPlaylist9 NO FILE 0 10 zzPlaylist10NO FILE 0 Query 2 select * from playlistTable order by cPlaylistName,iDirId collate nocase 30 APlayList30 NO FILE 0 28 CPlayList28 NO FILE 0 25 DPlayList25 NO FILE 0 1 PlayList1 NO FILE 0 11 PlayList11 NO FILE 0 12 PlayList12 NO FILE 0 13 PlayList13 NO FILE 0 14 PlayList14 NO FILE 0 15 PlayList15 NO FILE 0 16 PlayList16 NO FILE 0 17 PlayList17 NO FILE 0 18 PlayList18 NO FILE 0 19 PlayList19 NO FILE 0 20 PlayList20 NO FILE 0 21 PlayList21 NO FILE 0 22 PlayList22 NO FILE 0 23 PlayList23 NO FILE 0 24 PlayList24 NO FILE 0 4 PlayList4 NO FILE 0 5 PlayList5 NO FILE 0 6 PlayList6 NO FILE 0 7 PlayList7 NO FILE 0 9 YourPlaylist9 NO FILE 0 29 bPlayList29 NO FILE 0 26 cPlayList26 NO FILE 0 27 ccPlayList27NO FILE 0 3 myPlaylist3 NO FILE 0 2 playList2 NO FILE 0 8 yourplaylist8 NO FILE 0 10 zzPlaylist10NO FILE 0 CLARIFICATION REQUIRED: If I have a non-text column (say integer column like iDirId) at the end in the order by clause I find that collate nocase is not performed. Is this the expected behaviour? If so how could I have the nocase collation for the cPlaylistName even though I want the ordering sequence as above in Query 2? This is a sample table and I have different such tables where in I need to order by various text,integer columns in specific order but also want to collate the text strings in a case insensitive manner during this ordernig. I find one solution by using COLLATE NOCASE during the create table for as below. CREATE TABLE playlistTable( iPlaylistId INTEGER PRIMARY KEY,cPlaylistName VARCHAR(100) COLLATE NOCASE, cFileName VARCHAR(255),iDirId INTEGER ); Is this a possible solution? Please provide me details at the earliest as I need to finalise the queries for these tables at the earliest. Thanking for your support in advance, With regards, Sankara Narayanan B
