Re: [sqlite] combining multiple queries

[Jim Dodgen]

two tings I would do.

1. further normalize your tag field make it a table keyed by id and tag 
join to this in your query


2. for genre = "comedy" OR genre = "drama" I would  genre IN ("comedy", 
"drama")


you might consider further normalizing genre, to reduce duplication and 
also allow multiple genre for a film.


jason schleifer wrote:

heya folks,

I'm trying to figure out the proper syntax for doing a query of something
like this:

I have two different types of things that people will be querying for, 
and I

want to do an OR on each, but and AND between them..

So let's say i've got a table that looks like this:

Create TABLE film (id INTEGER PRIMARY KEY, genre VARCHAR, tag VARCHAR):

Genre can be of whatever genre the user wants.. comedy, drama, etc.
tag is a list of words that are separated by ;.. "kennedy;marilyn;funny;"
etc

If the user doesn't specify either genre or a tag when they're 
querying, I

want to return the whole list of films (that's an easy one).

If the user specifies one or more tags, I want to be able to return all
moves that match EITHER tag.. for example, if htey specify "carrey" and
"snow", it should return all movies that have either carrey or snow in 
the

list.

If they then specify a genre, I want it to return ONLY the movies in the
specified genre that match carrey or snow.

Then (just to make it more complicated), if they specify two genres, it
should return all movies in either of those genres that match either 
carrey

or snow.

Does this make sense?

I know how to string a whole bunch of LIKEs together with an OR to get 
it to

work so you could get it to match a genre or a tag.. but how do I do
something like:

SELECT * FROM film WHERE (genre = "comedy" OR genre = "drama") AND (tag =
"bill murry" OR tag =  "scarlett o'hara" OR tag = "silly");

any help is greatly appreciated..

cheers!
-jason



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] combining multiple queries

[jason schleifer]

heya folks,

I'm trying to figure out the proper syntax for doing a query of something
like this:

I have two different types of things that people will be querying for, and I
want to do an OR on each, but and AND between them..

So let's say i've got a table that looks like this:

Create TABLE film (id INTEGER PRIMARY KEY, genre VARCHAR, tag VARCHAR):

Genre can be of whatever genre the user wants.. comedy, drama, etc.
tag is a list of words that are separated by ;.. "kennedy;marilyn;funny;"
etc

If the user doesn't specify either genre or a tag when they're querying, I
want to return the whole list of films (that's an easy one).

If the user specifies one or more tags, I want to be able to return all
moves that match EITHER tag.. for example, if htey specify "carrey" and
"snow", it should return all movies that have either carrey or snow in the
list.

If they then specify a genre, I want it to return ONLY the movies in the
specified genre that match carrey or snow.

Then (just to make it more complicated), if they specify two genres, it
should return all movies in either of those genres that match either carrey
or snow.

Does this make sense?

I know how to string a whole bunch of LIKEs together with an OR to get it to
work so you could get it to match a genre or a tag.. but how do I do
something like:

SELECT * FROM film WHERE (genre = "comedy" OR genre = "drama") AND (tag =
"bill murry" OR tag =  "scarlett o'hara" OR tag = "silly");

any help is greatly appreciated..

cheers!
-jason
--
jason schleifer
ah-ni-may-tor | weirdo
http://jonhandhisdog.com/

[sqlite] Ответ: Re: [sqlite] count up how much an free space on DB

[Artem Yankovskiy]

Thanks Richard.
I will try.

--- [EMAIL PROTECTED] пишет:

> Artem Yankovskiy <[EMAIL PROTECTED]> wrote:
> > Question, whether probably to count up how much an
> > free space there is in this a DB?
> > 
> 
> If the first byte of the database file is called
> byte 0,
> then bytes 16 and 17 form a 16-bit big-endian
> integer
> which is the number of bytes per page.  Bytes 36-39
> form
> a 32-bit big-endian integer which is the number of
> unused
> pages in the database.  If you multiple those two
> numbers
> together, you will get the amount of reusable free
> space
> in the database file.
> 
> --
> D. Richard Hipp  <[EMAIL PROTECTED]>
> 
> 
>
-
> To unsubscribe, send email to
> [EMAIL PROTECTED]
>
-
> 
> 








Вы уже с Yahoo!? 
Испытайте обновленную и улучшенную. Yahoo! Почту! http://ru.mail.yahoo.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] disk I/O error writing files mounted via samba

[Nikki Locke]

Guy Hindell wrote:
> I would like to use sqlite (v3.3.8) on a linux box (fedora core 5) to 
> read/write a database file in a directory which is actually on a windows 
> share mounted via samba/cifs. I can open the file, and read from it, but 
> writing produces "disk I/O error" messages (SQLITE_IOERR error code). I 
> can write ordinary files on the share (echo "foo" > [share]/file.txt) so 
> it doesn't look like a permissions issue. Only one process is ever going 
> to access the file so I wouldn't expect locking issues. If I try turning 
> things around so that I build/run my sqlite program on windows and 
> access a file on a samba share exported from my linux box I can 
> read/write without any errors. 
>  
> Anyone tried this? I'd love to solve this problem as it will be the 
> final nail in the coffin for MSAccess in my app ;-) 

Is the database bigger than 2Gb? 

If so, have you got large file support in samba (lfs in the mount options)?

-- 
Nikki Locke, Trumphurst Ltd.  PC & Unix consultancy & programming
http://www.trumphurst.com/



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLiteJDBC is now 100% Pure Java

[David Crawshaw]

On 14/12/06, Christian Smith <[EMAIL PROTECTED]> wrote:

> http://www.zentus.com/sqlitejdbc

Is locking now handled by NestedVM so that the driver is useable with
native SQLite processes?


Yes, that's why it took me so long. :) I have implemented fcntl()
locking using java.nio.*, which means it requires Java 1.4 for file
locking. Amazingly Java did not support file locking before 1.4 and
when they did implement it they made a terrible mistake, reminiscent
of closing an FD on fcntl() locks.


> I believe it can be greatly reduced from the current ~ 800kb,

Still, it's smaller than Derby. A head to head might be nice.


It can be reduced to ~ 350kb with dynamic interpreter as Joe Wilson
suggested, but this will slow it down further. I will do a speed test
with derby when I am done with some other nagging bugs.

d

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLiteJDBC is now 100% Pure Java

[Christian Smith]

David Crawshaw uttered:


For several months now I have been providing a Java JDBC driver for
SQLite 3. Now I am happy to announce with v030, the completion of a
100% Pure Java version.

http://www.zentus.com/sqlitejdbc

This is accomplished with the support of the great NestedVM project.
The SQLite C source is compiled by gcc to a MIPS binary, that is then
dynamically interpreted on top of the Java runtime. This involves a
performance hit but greatly simplifies inclusion in current projects
and delivery cross-platform.



Is locking now handled by NestedVM so that the driver is useable with 
native SQLite processes?





The native JNI version has not been abandoned, and binaries are still
provided for Windows and Mac OS X. Linux can be directly compiled.

The new native version has been extensively tested, with the only
major caveat at the moment being the size of the binary. I believe it
can be greatly reduced from the current ~ 800kb, I just do not have
the time.



Still, it's smaller than Derby. A head to head might be nice.

Christian

--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] SQL extension

[Dusan Gibarac]

What are our options to extend or change SQL syntax if needed? 

Dusan


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: Re: Select with left outer join - Am I going mad ? Second edition

[Igor Tandetnik]

Alexandre Guion <[EMAIL PROTECTED]> wrote:

I tried this one of course, and it doesn't work, it could be a bug. It
returns every property for every medium (not just 'myprops')


This one is tested and appears to work:

SELECT
   m.MediumID AS id,
   IFNULL(MAX(pn.Text), '') AS name,
   IFNULL(MAX(CASE WHEN pn.Text IS NULL THEN NULL ELSE pv.Text END), 
'') AS value

FROM Media AS m
   LEFT JOIN MediumPropMap AS mpm ON mpm.MediumID=m.MediumID
   LEFT JOIN Property AS p ON (mpm.PropID=p.PropID AND p.UserID=1)
   LEFT JOIN PropName AS pn ON
   (p.PropNameID=pn.PropNameID AND pn.Text='myprop')
   LEFT JOIN PropValue AS pv ON p.PropValueID=pv.PropValueID

GROUP BY m.MediumID ORDER BY value;


Each group produced by GROUP BY can have only one non-NULL value in 
pn.Text (assuming each medium may have no more than one of each 
property). MAX aggregate is simply used to pick up this single non-NULL 
value, and the corresponding pv.Text


Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] GROUP BY in SQLite

[jose isaias cabrera]

Give each line a few extra spaces.  I don't know why, but I had the same 
error.  I gave a few extra spaces, specially at the end, and it worked just 
find...


- Original Message - 
From: <[EMAIL PROTECTED]>

To: 
Sent: Thursday, December 14, 2006 7:15 AM
Subject: [sqlite] GROUP BY in SQLite



Just wondering about the implementation of GROUP BY in SQLite.
It looks I can do:

select
field1,
field2,
field3
from
table
group by
field1

and I will get the last row in the field1 group.
This is fine and I can use that, but I thought that the SQL standard
was that all non-aggregate fields should be in the GROUP BY clause.
I just tried it in Interbase and the above construction indeed doesn't
work with the error:

SQL error code = -104, invalid column reference

Runs fine though in SQLite.

Is this a known feature?

RBS




-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] ANN: SQLite Maestro 6.12 released

[SQL Maestro Group]

Hi All,

SQL Maestro Group is happy to announce the release of SQLite Maestro 6.12, a 
powerful Windows GUI tool for SQLite databases administration and 
development. The new version is immediately available at

http://www.sqlmaestro.com/products/sqlite/maestro/

Also we're happy to grant you a time-limited 25% discount on all our 
products and bundles. Don't miss this unique chance to get our software at a 
special Christmas price!

http://www.sqlmaestro.com/purchase/

What's new/changed in version 6.12?

1. The main feature of the new version is the tabbed MDI interface 
implementation (like Mozilla Firefox). Here are some its advantages:

- you can drag-n-drop tabs to change their order;
- you can close the current tab pressing the mouse wheel (or the middle 
mouse button);

- you can drag-n-drop database objects or selected text between tabs;
- each tab has a popup menu, which allows you to close current tab, close 
all tabs or close all the tab except selected one (close other tabs).


2. SQL Script Editor: if a user opens a file larger than 100K, SQLite 
Maestro will suggest him to execute the script file without opening it in 
the editor. Of course it is also possible to execute any script file using 
this way: just press the "Execute script from file" link on the Script 
Editor navigation bar and specify the filename.


3. We've added an option for converting new object names to lower/upper 
case.


4. Data grid: the speed of data loading was significantly increased.

5. Some other improvements and small bugfixes.

Sincerely yours,
SQL Maestro Group
http://www.sqlmaestro.com 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Transpose table

[Kees Nuyt]

On Thu, 14 Dec 2006 09:53:22 - (GMT), you wrote:

>Hi Denis,
>
>Yes, it is a one-off action and the only purpose is to present the data
>into and Excel sheet in a more readable way.
>I had done your suggestion in VBA, but I thought it was a bit slow
>and wondered if there was a better way.

If Excel is the target anyway I guess the fasted way to do this
is to use the transpose option of the paste-special function in
Excel itself.

>I have just found a possible way to do this and maybe it is faster.
>Say I have a table with an ID column and 3 other columns.
>The data in these other columns need to be grouped by ID number, so
>
>ID col1 col2 col3
>
>would become:
>
>ID col1_1 col2_1 col_1  col1_2 col2_2 col3_2 col1_3 col2_3 col3_3
>
>etc. where the maximum number of fields will be determined by the
>maximum number of records for one ID number
>
>Now I found that if I do:
>select
>ID,
>col1,
>col2,
>col3
>from
>table
>group by
>ID
>
>Then it will always pick up the row that comes last in the group of
>ID numbers. This might actually be faster than doing a subquery with MAX.
>
>Now if I run the above and move the data to a new table, say table2 and
>then run a query like this:
>
>select
>t1.ID,
>t1.col1,
>t1.col2,
>t1.col3
>from
>table1 t1 inner join table2 t2 on
>(t1.ID = t2.ID)
>where
>t1.col1 < t2.col1
>group by
>t1.ID
>
>Then I will get the rows (if there was a row left)in the ID group
>that comes second from last, so
>
>ID
>1
>1
>1
>1 < will get this one
>1
>
>If I keep repeating this in a VBA loop and then join the tables I would
>get my output. Not sure it is faster, but I think it might.
>Will see.
>
>
>RBS
>
>> Hi RBS!
>>
>> If I understood you correctly you need a tool to transform these data
>> just once?
>> So there is a pseudocode describing one of possible approaches. To
>> convenient transformation SQLite is not enough for me, I suggest to use
>> any script language like Lua, Ptython, etc.
>>
>> 1) With a statement
>> SELECT COUNT(ID) AS counter FROM old GROUP BY ID ORDER BY counter DESC
>> LIMIT 1
>> Determine max number of a values
>>
>> 2) construct create table statement
>> CREATE TABLE new(
>>  ID INTEGER NOT NULL UNIQUE
>> for n=1, maxVal
>>  ", value TEXT"
>> end
>> );
>> and execute it
>>
>> 3) then navigate through 'old' table, create statements for insert data
>> to 'new'
>>
>>
>>
>> But please be sure that you need exactly such transformation. It is a
>> _denormalization_, almost anytime people try to perform conversion
>> exactly as you describe but in reverse direction :)
>>
>> With a 'new' table many operation, such as adding another one value for
>> ID = 1, will lead to ALTER TABLE ADD COLUMN, etc. You will come away
>> from SQL logic.
>>
>> Regards, Denis
>>
>> -Original Message-
>> From: RB Smissaert [mailto:[EMAIL PROTECTED]
>> Sent: Thursday, December 14, 2006 10:39 AM
>> To: sqlite-users@sqlite.org
>> Subject: RE: [sqlite] Transpose table
>>
>>
>> The example I gave shows exactly what I need to do.
>> I have a column of ID numbers with duplicates. I have to make this
>> column hold only unique ID numbers by moving the values to the first row
>> where that ID number appears, with that increasing the number of
>> columns. Hope this makes it a clearer.
>>
>> RBS
>>
>> -Original Message-
>> From: Darren Duncan [mailto:[EMAIL PROTECTED]
>> Sent: 14 December 2006 06:59
>> To: sqlite-users@sqlite.org
>> Subject: Re: [sqlite] Transpose table
>>
>> Can you please provide a use case for your example, so we know what
>> you're trying to accomplish?  That should help us to help you better.
>> -- Darren Duncan
>>
>> At 12:08 AM + 12/14/06, RB Smissaert wrote:
>>>I am moving my code away from VBA and transferring it to SQL. There is
>>>one particular routine where I haven't found a good replacement
>> for
>>>and that is to transpose a table from a vertical layout to a horizontal
>> one,
>> 
-- 
  (  Kees Nuyt
  )
c[_]

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] GROUP BY in SQLite

[Kees Nuyt]

Hi Bart,


First of all, you clearly try to start a new subject here, so
please don't reply to something completely different, but start
a new thread. TIA.

On Thu, 14 Dec 2006 12:15:57 - (GMT), you wrote:

>Just wondering about the implementation of GROUP BY in SQLite.
>It looks I can do:
>
>select
>field1,
>field2,
>field3
>from
>table
>group by
>field1
>
>and I will get the last row in the field1 group.

In SQL there isn't something like a "last row", unless you
specify ORDER BY. You get "some row", next time you run the
query you might get another one.

>This is fine and I can use that, but I thought that the SQL standard
>was that all non-aggregate fields should be in the GROUP BY clause.

That's right. So if you value your application you can't use it,
even though sqlite doesn't throw an error at the moment.

>I just tried it in Interbase and the above construction 
>indeed doesn't work with the error:
>
>SQL error code = -104, invalid column reference
>
>Runs fine though in SQLite.
>
>Is this a known feature?

I wouldn't call it a feature, and you'd better not rely on this
behaviour. 

>RBS

HTH
-- 
  (  Kees Nuyt
  )
c[_]

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Transpose table

[Denis Povshedny]

It's nice that problem was solved.

JFYI. In common, task for creating sparse matrix from plain sql
normalized table is very common for OLAP. Maybe you shall read something
about it if these task arised from time to time.

Best regards, Denis

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] 
Sent: Thursday, December 14, 2006 1:41 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Transpose table


Have tested this now and it seems to work fine.
Faster as well than my old method.

RBS


> Hi Denis,
>
> Yes, it is a one-off action and the only purpose is to present the 
> data into and Excel sheet in a more readable way. I had done your 
> suggestion in VBA, but I thought it was a bit slow and wondered if 
> there was a better way.
>
> I have just found a possible way to do this and maybe it is faster. 
> Say I have a table with an ID column and 3 other columns. The data in 
> these other columns need to be grouped by ID number, so
>
> ID col1 col2 col3
>
> would become:
>
> ID col1_1 col2_1 col_1  col1_2 col2_2 col3_2 col1_3 col2_3 col3_3
>
> etc. where the maximum number of fields will be determined by the 
> maximum number of records for one ID number
>
> Now I found that if I do:
> select
> ID,
> col1,
> col2,
> col3
> from
> table
> group by
> ID
>
> Then it will always pick up the row that comes last in the group of ID

> numbers. This might actually be faster than doing a subquery with MAX.
>
> Now if I run the above and move the data to a new table, say table2 
> and then run a query like this:
>
> select
> t1.ID,
> t1.col1,
> t1.col2,
> t1.col3
> from
> table1 t1 inner join table2 t2 on
> (t1.ID = t2.ID)
> where
> t1.col1 < t2.col1
> group by
> t1.ID
>
> Then I will get the rows (if there was a row left)in the ID group that

> comes second from last, so
>
> ID
> 1
> 1
> 1
> 1 < will get this one
> 1
>
> If I keep repeating this in a VBA loop and then join the tables I 
> would get my output. Not sure it is faster, but I think it might. Will

> see.
>
>
> RBS
>
>
>
>
>
>
>> Hi RBS!
>>
>> If I understood you correctly you need a tool to transform these data

>> just once? So there is a pseudocode describing one of possible 
>> approaches. To convenient transformation SQLite is not enough for me,

>> I suggest to use any script language like Lua, Ptython, etc.
>>
>> 1) With a statement
>> SELECT COUNT(ID) AS counter FROM old GROUP BY ID ORDER BY counter 
>> DESC LIMIT 1 Determine max number of a values
>>
>> 2) construct create table statement
>> CREATE TABLE new(
>>  ID INTEGER NOT NULL UNIQUE
>> for n=1, maxVal
>>  ", value TEXT"
>> end
>> );
>> and execute it
>>
>> 3) then navigate through 'old' table, create statements for insert 
>> data to 'new'
>>
>>
>>
>> But please be sure that you need exactly such transformation. It is a

>> _denormalization_, almost anytime people try to perform conversion 
>> exactly as you describe but in reverse direction :)
>>
>> With a 'new' table many operation, such as adding another one value 
>> for ID = 1, will lead to ALTER TABLE ADD COLUMN, etc. You will come 
>> away from SQL logic.
>>
>> Regards, Denis
>>
>> -Original Message-
>> From: RB Smissaert [mailto:[EMAIL PROTECTED]
>> Sent: Thursday, December 14, 2006 10:39 AM
>> To: sqlite-users@sqlite.org
>> Subject: RE: [sqlite] Transpose table
>>
>>
>> The example I gave shows exactly what I need to do.
>> I have a column of ID numbers with duplicates. I have to make this 
>> column hold only unique ID numbers by moving the values to the first 
>> row where that ID number appears, with that increasing the number of 
>> columns. Hope this makes it a clearer.
>>
>> RBS
>>
>> -Original Message-
>> From: Darren Duncan [mailto:[EMAIL PROTECTED]
>> Sent: 14 December 2006 06:59
>> To: sqlite-users@sqlite.org
>> Subject: Re: [sqlite] Transpose table
>>
>> Can you please provide a use case for your example, so we know what 
>> you're trying to accomplish?  That should help us to help you better.
>> -- Darren Duncan
>>
>> At 12:08 AM + 12/14/06, RB Smissaert wrote:
>>>I am moving my code away from VBA and transferring it to SQL. There 
>>>is one particular routine where I haven't found a good replacement
>> for
>>>and that is to transpose a table from a vertical layout to a 
>>>horizontal
>> one,
>> 
>>
>> -
>> ---
>> 
>> -
>> To unsubscribe, send email to [EMAIL PROTECTED]
>>

>> 
>> -
>>
>>
>>
>>
>> -
>> ---
>> -
>> To unsubscribe, send email to [EMAIL PROTECTED]
>>

>> -
>>
>>
>> -
>> 
>> To unsubscribe, send email to [EMAIL PROTECTED]
>>

Re: [sqlite] disk I/O error writing files mounted via samba

[drh]

Guy Hindell <[EMAIL PROTECTED]> wrote:
> I would like to use sqlite (v3.3.8) on a linux box (fedora core 5) to 
> read/write a database file in a directory which is actually on a windows 
> share mounted via samba/cifs. I can open the file, and read from it, but 
> writing produces "disk I/O error" messages (SQLITE_IOERR error code). I 
> can write ordinary files on the share (echo "foo" > [share]/file.txt) so 
> it doesn't look like a permissions issue. Only one process is ever going 
> to access the file so I wouldn't expect locking issues. If I try turning 
> things around so that I build/run my sqlite program on windows and 
> access a file on a samba share exported from my linux box I can 
> read/write without any errors.
> 

Please turn on extended result codes using

  sqlite3_extended_result_codes(db, 1)

Then tell me the detailed error code that results from this
error.  That will help to isolate the problem.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] count up how much an free space on DB

[drh]

Artem Yankovskiy <[EMAIL PROTECTED]> wrote:
> Question, whether probably to count up how much an
> free space there is in this a DB?
> 

If the first byte of the database file is called byte 0,
then bytes 16 and 17 form a 16-bit big-endian integer
which is the number of bytes per page.  Bytes 36-39 form
a 32-bit big-endian integer which is the number of unused
pages in the database.  If you multiple those two numbers
together, you will get the amount of reusable free space
in the database file.

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] GROUP BY in SQLite

[bartsmissaert]

Just wondering about the implementation of GROUP BY in SQLite.
It looks I can do:

select
field1,
field2,
field3
from
table
group by
field1

and I will get the last row in the field1 group.
This is fine and I can use that, but I thought that the SQL standard
was that all non-aggregate fields should be in the GROUP BY clause.
I just tried it in Interbase and the above construction indeed doesn't
work with the error:

SQL error code = -104, invalid column reference

Runs fine though in SQLite.

Is this a known feature?

RBS




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: File Syste

[Eduardo Morras]

At 22:03 13/12/2006, you wrote:

I am developing a File System, and I'd like to use B+ Tree and not lost time
and CPU understanding SQL...


Check HFS(16/32 bits) and HFS+(64 bits) filesystems from Apple, they 
use B+ trees. The code is open source and you can find it on Darwin repository.




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Transpose table

[bartsmissaert]

Have tested this now and it seems to work fine.
Faster as well than my old method.

RBS


> Hi Denis,
>
> Yes, it is a one-off action and the only purpose is to present the data
> into and Excel sheet in a more readable way.
> I had done your suggestion in VBA, but I thought it was a bit slow
> and wondered if there was a better way.
>
> I have just found a possible way to do this and maybe it is faster.
> Say I have a table with an ID column and 3 other columns.
> The data in these other columns need to be grouped by ID number, so
>
> ID col1 col2 col3
>
> would become:
>
> ID col1_1 col2_1 col_1  col1_2 col2_2 col3_2 col1_3 col2_3 col3_3
>
> etc. where the maximum number of fields will be determined by the
> maximum number of records for one ID number
>
> Now I found that if I do:
> select
> ID,
> col1,
> col2,
> col3
> from
> table
> group by
> ID
>
> Then it will always pick up the row that comes last in the group of
> ID numbers. This might actually be faster than doing a subquery with MAX.
>
> Now if I run the above and move the data to a new table, say table2 and
> then run a query like this:
>
> select
> t1.ID,
> t1.col1,
> t1.col2,
> t1.col3
> from
> table1 t1 inner join table2 t2 on
> (t1.ID = t2.ID)
> where
> t1.col1 < t2.col1
> group by
> t1.ID
>
> Then I will get the rows (if there was a row left)in the ID group
> that comes second from last, so
>
> ID
> 1
> 1
> 1
> 1 < will get this one
> 1
>
> If I keep repeating this in a VBA loop and then join the tables I would
> get my output. Not sure it is faster, but I think it might.
> Will see.
>
>
> RBS
>
>
>
>
>
>
>> Hi RBS!
>>
>> If I understood you correctly you need a tool to transform these data
>> just once?
>> So there is a pseudocode describing one of possible approaches. To
>> convenient transformation SQLite is not enough for me, I suggest to use
>> any script language like Lua, Ptython, etc.
>>
>> 1) With a statement
>> SELECT COUNT(ID) AS counter FROM old GROUP BY ID ORDER BY counter DESC
>> LIMIT 1
>> Determine max number of a values
>>
>> 2) construct create table statement
>> CREATE TABLE new(
>>  ID INTEGER NOT NULL UNIQUE
>> for n=1, maxVal
>>  ", value TEXT"
>> end
>> );
>> and execute it
>>
>> 3) then navigate through 'old' table, create statements for insert data
>> to 'new'
>>
>>
>>
>> But please be sure that you need exactly such transformation. It is a
>> _denormalization_, almost anytime people try to perform conversion
>> exactly as you describe but in reverse direction :)
>>
>> With a 'new' table many operation, such as adding another one value for
>> ID = 1, will lead to ALTER TABLE ADD COLUMN, etc. You will come away
>> from SQL logic.
>>
>> Regards, Denis
>>
>> -Original Message-
>> From: RB Smissaert [mailto:[EMAIL PROTECTED]
>> Sent: Thursday, December 14, 2006 10:39 AM
>> To: sqlite-users@sqlite.org
>> Subject: RE: [sqlite] Transpose table
>>
>>
>> The example I gave shows exactly what I need to do.
>> I have a column of ID numbers with duplicates. I have to make this
>> column hold only unique ID numbers by moving the values to the first row
>> where that ID number appears, with that increasing the number of
>> columns. Hope this makes it a clearer.
>>
>> RBS
>>
>> -Original Message-
>> From: Darren Duncan [mailto:[EMAIL PROTECTED]
>> Sent: 14 December 2006 06:59
>> To: sqlite-users@sqlite.org
>> Subject: Re: [sqlite] Transpose table
>>
>> Can you please provide a use case for your example, so we know what
>> you're trying to accomplish?  That should help us to help you better.
>> -- Darren Duncan
>>
>> At 12:08 AM + 12/14/06, RB Smissaert wrote:
>>>I am moving my code away from VBA and transferring it to SQL. There is
>>>one particular routine where I haven't found a good replacement
>> for
>>>and that is to transpose a table from a vertical layout to a horizontal
>> one,
>> 
>>
>> 
>> 
>> -
>> To unsubscribe, send email to [EMAIL PROTECTED]
>> 
>> 
>> -
>>
>>
>>
>>
>> 
>> -
>> To unsubscribe, send email to [EMAIL PROTECTED]
>> 
>> -
>>
>>
>> -
>> To unsubscribe, send email to [EMAIL PROTECTED]
>> -
>>
>>
>>
>
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] building sqlite on windows in Unicode

[Christian Smith]

Check the requirements in:
http://www.sqlite.org/copyright.html

for patches and other submissions to SQLite. This could be what is holding 
up inclusion of the patch.


Christian

Brodie Thiesfield uttered:


Hi,

Building sqlite on windows in Unicode mode broke with the addition of
the loadable extensions. I found a bug matching this problem and
attached a patch to it to fix it a while ago, however I haven't seen any
other comments or movement in the bug. I'm not sure what else needs to
be done to have a patch accepted, so I'm posting here in the hope to
prod it along for review or acceptance.

The problem is that the dlopen/LoadLibrary code looks like it was hacked
in instead of being added to the platform abstraction API and it doesn't
support windows unicode builds out of the box. The patch fixes that
problem and silences a warning generated by the new index format.

bug...
http://www.sqlite.org/cvstrac/tktview?tn=2023

patch...
http://www.sqlite.org/cvstrac/attach_get/309/sqlite3.patch

Regards,
Brodie

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Transpose table

[bartsmissaert]

Hi Denis,

Yes, it is a one-off action and the only purpose is to present the data
into and Excel sheet in a more readable way.
I had done your suggestion in VBA, but I thought it was a bit slow
and wondered if there was a better way.

I have just found a possible way to do this and maybe it is faster.
Say I have a table with an ID column and 3 other columns.
The data in these other columns need to be grouped by ID number, so

ID col1 col2 col3

would become:

ID col1_1 col2_1 col_1  col1_2 col2_2 col3_2 col1_3 col2_3 col3_3

etc. where the maximum number of fields will be determined by the
maximum number of records for one ID number

Now I found that if I do:
select
ID,
col1,
col2,
col3
from
table
group by
ID

Then it will always pick up the row that comes last in the group of
ID numbers. This might actually be faster than doing a subquery with MAX.

Now if I run the above and move the data to a new table, say table2 and
then run a query like this:

select
t1.ID,
t1.col1,
t1.col2,
t1.col3
from
table1 t1 inner join table2 t2 on
(t1.ID = t2.ID)
where
t1.col1 < t2.col1
group by
t1.ID

Then I will get the rows (if there was a row left)in the ID group
that comes second from last, so

ID
1
1
1
1 < will get this one
1

If I keep repeating this in a VBA loop and then join the tables I would
get my output. Not sure it is faster, but I think it might.
Will see.


RBS






> Hi RBS!
>
> If I understood you correctly you need a tool to transform these data
> just once?
> So there is a pseudocode describing one of possible approaches. To
> convenient transformation SQLite is not enough for me, I suggest to use
> any script language like Lua, Ptython, etc.
>
> 1) With a statement
> SELECT COUNT(ID) AS counter FROM old GROUP BY ID ORDER BY counter DESC
> LIMIT 1
> Determine max number of a values
>
> 2) construct create table statement
> CREATE TABLE new(
>   ID INTEGER NOT NULL UNIQUE
> for n=1, maxVal
>   ", value TEXT"
> end
> );
> and execute it
>
> 3) then navigate through 'old' table, create statements for insert data
> to 'new'
>
>
>
> But please be sure that you need exactly such transformation. It is a
> _denormalization_, almost anytime people try to perform conversion
> exactly as you describe but in reverse direction :)
>
> With a 'new' table many operation, such as adding another one value for
> ID = 1, will lead to ALTER TABLE ADD COLUMN, etc. You will come away
> from SQL logic.
>
> Regards, Denis
>
> -Original Message-
> From: RB Smissaert [mailto:[EMAIL PROTECTED]
> Sent: Thursday, December 14, 2006 10:39 AM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] Transpose table
>
>
> The example I gave shows exactly what I need to do.
> I have a column of ID numbers with duplicates. I have to make this
> column hold only unique ID numbers by moving the values to the first row
> where that ID number appears, with that increasing the number of
> columns. Hope this makes it a clearer.
>
> RBS
>
> -Original Message-
> From: Darren Duncan [mailto:[EMAIL PROTECTED]
> Sent: 14 December 2006 06:59
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Transpose table
>
> Can you please provide a use case for your example, so we know what
> you're trying to accomplish?  That should help us to help you better.
> -- Darren Duncan
>
> At 12:08 AM + 12/14/06, RB Smissaert wrote:
>>I am moving my code away from VBA and transferring it to SQL. There is
>>one particular routine where I haven't found a good replacement
> for
>>and that is to transpose a table from a vertical layout to a horizontal
> one,
> 
>
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> 
> 
> -
>
>
>
>
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> 
> -
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] count up how much an free space on DB

[Artem Yankovskiy]

Hello.
There is a DB. auto_vacuum is not used. One of tables
of a database contains 500 records. 
We for example delete from it 300 records. The place
in a database was released, but the size of a file has
not decreased how is not set auto_vacuum.
Question, whether probably to count up how much an
free space there is in this a DB?








Вы уже с Yahoo!? 
Испытайте обновленную и улучшенную. Yahoo! Почту! http://ru.mail.yahoo.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-