Re: [sqlite] Is there a way to return the row number? (NOTthe rowid)

[Jay A. Kreibich]

On Mon, Jul 01, 2013 at 10:52:20PM -0400, James K. Lowden scratched on the wall:

> "select *" is shorthand for "all columns". You'll note that what's
> returned isn't some kind of special '*' column, but all columns.  The
> order in which the columns are returned isn't meaningful because the
> colums have labels -- names -- to tell you which is which.  Rearranging
> the column order doesn't change the answer.  

  That's not quite true.

  What you say is more or less true in pure Relational Theory.  Under
  Relational Theory, relational attributes (columns) are a proper set.
  The columns have no defined order (just as rows have no defined
  order), and can only be definitively reference by name.
 
  In SQL, columns are *not* a set.  The order of the columns in any SQL
  query or operation is strictly defined.  Columns cannot be referenced
  by name, because SQL allows name-less columns (SELECT 1, 1, 1;) and
  multiple columns with the same name (SELECT 1 A, 1 A, 1 A;).  SQL
  doesn't even strictly define the column name for a calculated column
  (SELECT avg( 1 )) and allows the DB to make up its own names.  SQLite
  used to have several PRAGMAs to control short and long column names.
  
  Rather, in SQL, a column is definitively defined by its positional
  index in the table or result set.  This is also why so many SQL APIs
  allow you to fetch column values by index, rather than by name (which
  would be a totally broken and dangerous API if columns could move
  around).  It gets pretty messy...  The SQL standard goes to some
  length to define a specific column order for stuff like JOIN operations,
  including edge-case details like NATURAL JOINs where the number of
  columns is reduced and somewhat ambiguously named.

  While rearranging the column order may not functionally change the
  answer, a database is not given that flexibility in SQL.  For
  example, "SELECT *" *must* return the columns in the order they are
  defined in the table definition.  It isn't that most databases just
  happen to do this-- the column order is actually predicated by the
  standard.

> "sort by *" would imply that the order of the columns returned by '*' is
> meaningful, which it is not.  "sort by the arbitrary order produced by
> 'select *'" isn't even deterministic.  

  In SQL column order *is* deterministic, so the sort order would also
  be deterministic.  Likely meaningless, but still deterministic.


   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to return the row number? (NOTthe rowid)

[Roman Fleysher]

Bravo Alex !!


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Alex Bowden [a...@designlifecycle.com]
Sent: Monday, July 01, 2013 12:34 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Is there a way to return the row number? (NOTthe  rowid)

> OK, you don't agree.  Your opinion!  (That doesn't make you right, though!)

If you approach the government france and explain that you're not really very 
good at French,  but if they made the following list of Chinese language 
inclusions into French, then it would make it easier for you to write your 
"thank you letter" to your auntie Marie,  than I'm sure they would oblige.

And even if they didn't it wouldn't make you wrong.

You are proposing changes to a relational database management system that 
damage its relationality, on the basis that you don't understand how to use 
relational databases properly, and it would make your life easier.

In defending maintaining the relationality, I don't really need to be right.  
The onus is on you to justify the change that you propose.


On 1 Jul 2013, at 16:36, Tony Papadimitriou  wrote:

> OK, you don't agree.  Your opinion!  (That doesn't make you right, though!)
>
>> I'm sure there will be a SQL engine somewhere that will do it for you.
>
> We're talking about SQLite here, aren't we?  If some other database can do 
> it, then you should also consider that it may also be able to do what this 
> 'row' function.  (e.g. MySQL).  So, your point it moot.
>
>>> So, why make it sound like I don't know what I'm talking about?
>> I think you beat me to it.
>
> No comment!
>
> -Original Message- From: Alex Bowden
> Sent: Monday, July 01, 2013 6:17 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Is there a way to return the row number? (NOTthe rowid)
>
> ___
> 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


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Another 2 questions about SQLite

[Igor Tandetnik]

On 7/1/2013 11:30 PM, James K. Lowden wrote:

Restricting ourselves to one process, I can think of two ways that
might go:

1.  With two connections, one connection or the other will wait.
SELECT will return 0 or 10 rows.


Yes, between two connection, normal transaction isolation rules apply, 
whether these connections are established by the same or separate processes.



2.  Trying to re-use a single connection to issue a second query
before finalizing the first one should return an error


No it should not, and does not. Try it.


because the library is being improperly used.


That may be, but it doesn't result in an error.


 SELECT will return 0 or 10 rows, or an error.


Not necessarily. Again, try it, see for yourself.


So I still don't see how the SELECT could be anything undeterministic.


You are laboring under incorrect assumptions, which would allow you to 
justify any conclusion.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Another 2 questions about SQLite

[James K. Lowden]

On Mon, 01 Jul 2013 23:00:27 -0400
Igor Tandetnik  wrote:
> On 7/1/2013 10:33 PM, James K. Lowden wrote:
> > Igor Tandetnik  wrote:
> >> If you change data that a live SELECT statement is iterating over,
> >> the outcome is unpredictable. It may appear to work, it may skip
> >> rows, it may return some rows more than once
> >
> > Really?  That's not what SERIALIZABLE means.

> > you're saying the SELECT statement may fetch any number of rows
> > between 0 and 10?
> 
> "Different process" is a key phrase here. No, the delete statement
> won't be able to commit while the select statement keeps the reader 
> transaction open.

Thank you for clarifying that.  Order is restored in my universe.  ;-)  

> The situation would be different if a single process, while half-way 
> through iterating over the select statement, would issue the delete 
> statement. 

Restricting ourselves to one process, I can think of two ways that
might go:

1.  With two connections, one connection or the other will wait.
SELECT will return 0 or 10 rows.   

2.  Trying to re-use a single connection to issue a second query
before finalizing the first one should return an error, because the
library is being improperly used.  SELECT will return 0 or 10 rows, or
an error.  

So I still don't see how the SELECT could be anything undeterministic.  

--jkl

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Another 2 questions about SQLite

[Igor Tandetnik]

On 7/1/2013 10:33 PM, James K. Lowden wrote:

On Sun, 30 Jun 2013 23:27:23 -0400
Igor Tandetnik  wrote:

If you change data that a live SELECT statement is iterating over,
the outcome is unpredictable. It may appear to work, it may skip
rows, it may return some rows more than once


Really?  That's not what SERIALIZABLE means.


SERIALIZABLE is a transaction isolation level. It applies to interaction 
between two transactions on two separate connections. It is perfectly 
normal and expected for a single transaction to be affected by changes 
it itself makes.



If I have 10-row table T and issue

select * from T;

and from a different process issue

delete * from T;

you're saying the SELECT statement may fetch any number of rows
between 0 and 10?


"Different process" is a key phrase here. No, the delete statement won't 
be able to commit while the select statement keeps the reader 
transaction open.


The situation would be different if a single process, while half-way 
through iterating over the select statement, would issue the delete 
statement. This is roughly similar to what the OP is trying to do, as 
far as I can tell.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

[James K. Lowden]

On Mon, 01 Jul 2013 13:10:59 +0200
Gabriel Corneanu  wrote:

> I also needed this for some special update queries.
> Without many details, it was some kind of "insert into xxx select 
> , otherfields from source order by ".
> For this case there is a workaround, selecting first into a temporary 
> table with auto generated rowid and using it afterwards for insert.

Ranking the rows requires nothing more than joining the table to
itself.  You might find http://www.schemamania.org/sql/#rank.rows
helpful.  

--jkl
 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to return the row number? (NOTthe rowid)

[James K. Lowden]

On Mon, 1 Jul 2013 14:22:53 +0300
"Tony Papadimitriou"  wrote:

> Just because you can select something doesn't mean you have to be 
> able to sort by it.  

Yes, it does. 

> Can you sort by * (select * by table sort by *)?  

You have confused syntax with semantics.  

"select *" is shorthand for "all columns". You'll note that what's
returned isn't some kind of special '*' column, but all columns.  The
order in which the columns are returned isn't meaningful because the
colums have labels -- names -- to tell you which is which.  Rearranging
the column order doesn't change the answer.  

"sort by *" would imply that the order of the columns returned by '*' is
meaningful, which it is not.  "sort by the arbitrary order produced by
'select *'" isn't even deterministic.  

> So, why make it sound like I don't know what I'm talking about?

Ahem.  

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Another 2 questions about SQLite

[James K. Lowden]

On Sun, 30 Jun 2013 23:27:23 -0400
Igor Tandetnik  wrote:

> On 6/30/2013 11:13 PM, Igor Korot wrote:
> > Well I will use another statement variable as in the sample code.
> > My questions was: if I call delete on the record that was just
> > retrieved in another query will this delete affects it?
> 
> If you change data that a live SELECT statement is iterating over,
> the outcome is unpredictable. It may appear to work, it may skip
> rows, it may return some rows more than once

Really?  That's not what SERIALIZABLE means.  

If I have 10-row table T and issue 

select * from T;

and from a different process issue

delete * from T;

you're saying the SELECT statement may fetch any number of rows
between 0 and 10?  

--jkl

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to return the row number? (NOTthe rowid)

[Keith Medcalf]

Yes, you can sort by *

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Tony Papadimitriou
> Sent: Monday, 1 July, 2013 05:23
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Is there a way to return the row number? (NOTthe
> rowid)
> 
> Please!  Just because you can select something doesn't mean you have
> to be
> able to sort by it.  Can you sort by *
> (select * by table sort by *)?  So, why make it sound like I don't
> know what
> I'm talking about?
> 
> -Original Message-
> From: Alex Bowden
> Sent: Monday, July 01, 2013 2:07 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Is there a way to return the row number? (NOTthe
> rowid)
> 
> > This would not be something you would sort by.
> 
> And what if I do?
> 
> > It should be assigned a value only during final 'display' of the
> query
> > after all 'sorts' of operations are done with.
> 
> Oh great.  So the user is supposed to understand the implementation,
> in
> order to understand what the results will be.
> 
> 
> This would be just another nail in the coffin of relationality and
> simplicity, on a minor whim.
> 
> ___
> 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] Is there a way to return the row number? (NOT the rowid)

[Keith Medcalf]

rownumber = 0
do while ThereAreRows:
   sqlite3_step ...
   rownumber += 1
   ...

pretty simple eh?

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Tony Papadimitriou
> Sent: Monday, 1 July, 2013 03:33
> To: General Discussion of SQLite Database
> Subject: [sqlite] Is there a way to return the row number? (NOT the
> rowid)
> 
> Is there a function (or method), e.g., row(), to return the sequence
> number
> of the selected row?  This is not the same as ROWID.  row() should
> give a
> sequence number always starting from 1 up the to the number of rows
> returned
> by the view/select etc.
> 
> If not, then please add to the wish list.
> 
> TIA
> 
> ___
> 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] assertion in sqlite 3.7.17 for rather simple SELECT query

[Richard Hipp]

On Mon, Jul 1, 2013 at 9:20 AM, Jens Miltner  wrote:

> I recently updated our SQLite source to SQLite source distribution 3.7.17.
> Since then, we get assertions when running one of our larger queries. I
> have stripped down the query to a bare minimum query that produces the same
> assertion:
>


Thanks for an providing such a clean and easy to reproduce bug report.

A patch to fix this problem has been checked into trunk.  (
http://www.sqlite.org/src/info/72919ec34f).  The patch should apply cleanly
to whatever version of SQLite you are using.

At http://www.sqlite.org/draft/download.html you can find snapshots of the
version 3.8.0 alpha version that includes this patch.



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GUI for SQLite (nobre)

[joe.fis...@tanguaylab.com]

SQLite Expert (Windows) is good and I've been using it for the last 2 
years but unfortunately it only does (Windows).

Although, it also runs good in WINE on Linux.
I've recently started using Valentina Studio (Linux/Windows/Mac) because 
I need to also run on native Linux, and if I get rich I'll buy a Mac.

Could someone report back any feedback on how it runs under Mac?
http://www.valentina-db.com/en/all-downloads


Joe Fisher
Oregon State University

---
Subject: Re: [sqlite] GUI for SQLite
Message-ID: <1372448971371-69729.p...@n5.nabble.com>
Content-Type: text/plain; charset=us-ascii
I've been impressed by sqliteexpert Personal Edition (for Windows)
I haven't found a great one for OSX yet.
-

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to return the row number? (NOTthe rowid)

[Alex Bowden]

> OK, you don't agree.  Your opinion!  (That doesn't make you right, though!)

If you approach the government france and explain that you're not really very 
good at French,  but if they made the following list of Chinese language 
inclusions into French, then it would make it easier for you to write your 
"thank you letter" to your auntie Marie,  than I'm sure they would oblige.

And even if they didn't it wouldn't make you wrong.

You are proposing changes to a relational database management system that 
damage its relationality, on the basis that you don't understand how to use 
relational databases properly, and it would make your life easier.

In defending maintaining the relationality, I don't really need to be right.  
The onus is on you to justify the change that you propose.


On 1 Jul 2013, at 16:36, Tony Papadimitriou  wrote:

> OK, you don't agree.  Your opinion!  (That doesn't make you right, though!)
> 
>> I'm sure there will be a SQL engine somewhere that will do it for you.
> 
> We're talking about SQLite here, aren't we?  If some other database can do 
> it, then you should also consider that it may also be able to do what this 
> 'row' function.  (e.g. MySQL).  So, your point it moot.
> 
>>> So, why make it sound like I don't know what I'm talking about?
>> I think you beat me to it.
> 
> No comment!
> 
> -Original Message- From: Alex Bowden
> Sent: Monday, July 01, 2013 6:17 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Is there a way to return the row number? (NOTthe rowid)
> 
> ___
> 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] Is there a way to return the row number? (NOTthe rowid)

[Tony Papadimitriou]

OK, you don't agree.  Your opinion!  (That doesn't make you right, though!)


I'm sure there will be a SQL engine somewhere that will do it for you.


We're talking about SQLite here, aren't we?  If some other database can do 
it, then you should also consider that it may also be able to do what this 
'row' function.  (e.g. MySQL).  So, your point it moot.



So, why make it sound like I don't know what I'm talking about?

I think you beat me to it.


No comment!

-Original Message- 
From: Alex Bowden

Sent: Monday, July 01, 2013 6:17 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Is there a way to return the row number? (NOTthe 
rowid)


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

[Tony Papadimitriou]

I agree with you completely!  It has to be relatively simple to implement. 
And certainly it adds a very important missing functionality (for those who 
can see it, that is).
Some kind of internal cursor has to iterate to return rows to the user after 
the query is completely executed.  This can add a counter (if it doesn't 
have it already to compare again a possible LIMIT).


The only real problem is how do you return this to the user.  If you put it 
in a function, you have to syntactically exclude that function from 
appearing anywhere except the select fields (not in WHERE, ORDER, GROUP or 
whatever else.)  I assume a special name or symbol (#, perhaps) would work. 
(e.g.., SELECT # FROM table _or_ SELECT # AS row,* FROM table -- to give it 
a name)


As for how useful it is, it depends on one's imagination!  If some can't see 
a reason for certain functionality, that's no reason for it not to be there. 
Some do see a reason, and (please) don't call it a whim!


One good example was mentioned in someone else's reply, you need to add 
ranks to a master table based on periodical results.  Yes, you could use 
temporary tables as intermediate storage but what if you have a table 100K 
rows or more?  Not very efficient, is it?


By the way, MySQL can do it very easily because of its variable manipulation 
ability.  (Something like SELECT @ROW:=@ROW+1 as ROW,* FROM table,(SELECT 
@ROW := 0) foo; )  By since SQLite has not such capability, a poor man's 
version could be implemented, as suggested.


Thanks to all for your contribution!

-Original Message- 
From: Gabriel Corneanu

Sent: Monday, July 01, 2013 4:54 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Is there a way to return the row number? (NOT 
therowid)


As I don't think that the implementation is the problem, the real issues
are:
a) is it "lite" enough for the core? (I believe yes)
b) is a specific (non-standard) feature desired? (I don't have a
problem, but maintainers might have)

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to return the row number? (NOTthe rowid)

[Alex Bowden]

> Please!  Just because you can select something doesn't mean you have to be 
> able to sort by it.

There are a small number of exceptions,  each of which is a bodge.

But some bodges are worth the impact.

>  Can you sort by *

* is a very useful and largely harmless bodge.

There is fundamentally no reason why you shouldn't be able to to sort by *.  
But people usually care about the order of field in a sort.  

> (select * by table sort by *)?  

I'm sure there will be a SQL engine somewhere that will do it for you.

It would require no additional documentation or code.  In fact it might require 
extra, to prevent it.  It's pointless,  but benign.

> So, why make it sound like I don't know what I'm talking about?

I think you beat me to it.

> 
> -Original Message- From: Alex Bowden
> Sent: Monday, July 01, 2013 2:07 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Is there a way to return the row number? (NOTthe rowid)
> 
>> This would not be something you would sort by.
> 
> And what if I do?
> 
>> It should be assigned a value only during final 'display' of the query after 
>> all 'sorts' of operations are done with.
> 
> Oh great.  So the user is supposed to understand the implementation, in order 
> to understand what the results will be.
> 
> 
> This would be just another nail in the coffin of relationality and 
> simplicity, on a minor whim.
> 
> ___
> 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] Is there a way to return the row number? (NOT the rowid)

[Simon Slavin]

On 1 Jul 2013, at 2:54pm, Gabriel Corneanu  wrote:

> While I agree non-standard features should be kept to minimum possible, I 
> needed this so often that I simply think there is a larger potential benefit.
> Another example: I don't do web design, but I remember some WYSIWYG editors 
> (Macromedia Dreamweaver) where it was easily possible to generate HTML tables 
> (paged listings) from result sets. There was no code to write, just mark some 
> fields from the result set.

I /do/ write web interfaces for databases kept in SQLite.  I do that a lot.  
There is no advantage to me of knowing the 'line number' of an entry returned 
by a SELECT.  What I need to know about the table is the rowid of the row in 
the database and I find that by asking for it explicitly:

SELECT rowid,aColumn,anotherColumn FROM MyTable

I sometimes store that rowid in the HTML code I make up my table with:

newRow = document.createElement('tr')
newRow.id = 'teamsRow'+teamRow.rowid

This makes it easy to find the row on the page to change if underlying data 
changes, and to know which row of the database to change if the user wants to 
change something in the table.  But I can’t think of any situation in which I 
would want to use the number of the row in the returned set.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

[Gabriel Corneanu]

Hi Simon,

No problem, but I don't understand what has this to do with multi - 
processing / users?? (the query program has a lock on the table anyway).
The counter can be either reused (see the other "LIMIT" discussion) or 
it needs a different implementation.


As I don't think that the implementation is the problem, the real issues 
are:

a) is it "lite" enough for the core? (I believe yes)
b) is a specific (non-standard) feature desired? (I don't have a 
problem, but maintainers might have)


While I agree non-standard features should be kept to minimum possible, 
I needed this so often that I simply think there is a larger potential 
benefit.
Another example: I don't do web design, but I remember some WYSIWYG 
editors (Macromedia Dreamweaver) where it was easily possible to 
generate HTML tables (paged listings) from result sets. There was no 
code to write, just mark some fields from the result set.

I'm sure other people can give more details about this...

Regards,
Gabriel

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to return the row number? (NOT therowid)

[Gabriel Corneanu]

Hi,

My opinion is a little different:

a) of course the compiler needs to change the query program (only if 
"nrow" is requested/used)
b) I don't know the internals, but I just can't believe that such a 
value could not be exported somehow
c) I understand it would be non-standard; however there are lots of 
other specific features to sqlite
d) in certain scenarios, the alternatives are quite difficult to 
achieve; e.g. you have a simple batch file to make some updates, no 
programming involved...


I believe it's quite easy to do (technically), but it's just a matter of 
whether it's "lite" enough :) for the source code...


Regards,
Gabriel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

[Simon Slavin]

On 1 Jul 2013, at 2:00pm, Gabriel Corneanu  wrote:

> As I wrote it's easy to do when you control the loop.
> Sometimes there is either no explicit loop (direct sql, no programming code) 
> or the loop is out of your reach (3rd party library).

Sorry, I posted that before seeing the rest of the thread which built up 
afterwards.  Nevertheless all solutions I see fail in systems with 
multiprocessing or multiple users, where the database may be edited between 
when the numbers are produced and when they’re used.  It’s much better to have 
your software number the rows itself, or to use SQLite to store your ordinals 
in the tables.


On 1 Jul 2013, at 12:19pm, Dave Wellman  wrote:

> Where the "row number in the answer set" does come in useful (or at least
> where I've used it a number of itmes) is when populating a table with the
> results of a select and including this value as another column in that
> table.
> 
> "build a table with the top 10 selling items over the last week"
> 
> Insert into target-table
> Select productid
>,sum(sales)
>   ,row-number-in-answer-set as sales_rank
> From sales_table...

Do

SELECT productid FROM sales_table ORDER BY total_sales DESC LIMIT 10

Built the answers up in an internal variable, then use UPDATE to write the 
ranks back out again.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] assertion in sqlite 3.7.17 for rather simple SELECT query

[Jens Miltner]

I recently updated our SQLite source to SQLite source distribution 3.7.17. 
Since then, we get assertions when running one of our larger queries. I have 
stripped down the query to a bare minimum query that produces the same 
assertion:

The following rather simple SELECT query gives me an assertion in sqlite3.c 
line 109259:

CREATE TABLE p (
  id INTEGER PRIMARY KEY,
  uid VARCHAR(36),
  t INTEGER
);

CREATE TABLE pa (
  id INTEGER PRIMARY KEY,
  p_id INTEGER,
  a_uid VARCHAR(36)
);

CREATE TABLE a (
  id INTEGER PRIMARY KEY,
  uid VARCHAR(36),
  t INTEGER
);

SELECT DISTINCT
  pa.p_id AS pid,
  a.uid AS a_uniqueid,
  p.t AS p_t
FROM 
  pa 
  LEFT JOIN p ON
p.uid='1234'
  LEFT JOIN a ON 
a.uid=pa.a_uid
WHERE
  a.t=p.t
;


Running this SELECT query causes the following assertion to fire:

Gandalf:~ jens$ sqlite3 < /tmp/foo.sql/
sqlite3.c:109259: failed assertion `(pTerm->prereqRight & newNotReady)!=0'
Assertion failed: (libcompiler_rt abort), function __eprintf, file 
/SourceCache/clang/clang-425.0.27/src/projects/compiler-rt/lib/eprintf.c, line 
31.
Abort trap: 6


I took a look at the source code, but unfortunately, I cannot deduce what's 
going wrong in that function :(

All I found is that when I change the order of the JOIN statements, the query 
works. Unfortunately, while this seems easy in this reduced example, in real 
world, I cannot easily change the order of the JOINs due to other restrictions.

A google query for the expression used in the assertion showed that the block 
of code containing the assertion was introduced in Check-in [5f4907e1c6] 
... doesn't help me much either :(


Version Info:
Gandalf:~ jens$ sqlite3 -version
3.7.17 2013-05-20 00:56:22 118a3b35693b134d56ebd780123b7fd6f1497668

(This is on Mac OS X, compiling with Apple LLVM version 4.2 (clang-425.0.27) 
(based on LLVM 3.2svn) for 32-bit Intel architecture, in case this matters)


Any help welcome...

Thanks,
-jens

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to return the row number? (NOT therowid)

[Hick Gunter]

The problem is
a) you must have a limit clause for the register to even be tallied
b) the register is not part of the result set
c) it would be a very incompatible change to SQL syntax
d) there are other ways of achieving the same result, either in the caller or 
in a user written function

explain select * from test limit 3,4;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 000  NULL
1 Integer4 1 000  LIMIT counter
2 Integer3 2 000  NULL
3 MustBeInt  2 0 000  OFFSET counter
4 IfPos  2 6 000  NULL
5 Integer0 2 000  NULL
6 Add1 2 300  LIMIT+OFFSET
7 IfPos  1 9 000  NULL
8 Integer-13 000  NULL
9 Goto   0 23000  NULL
10OpenRead   0 2 1 3  00  test
11Rewind 0 21000  NULL
12AddImm 2 -1000  NULL
13IfNeg  2 15000  NULL
14Goto   0 20000  skip OFFSET records
15Column 0 0 400  test.a
16Column 0 1 500  test.b
17Column 0 2 600  test.c
18ResultRow  4 3 000  NULL
19IfZero 1 21-1   00  NULL
20Next   0 12001  NULL
21Close  0 0 000  NULL
22Halt   0 0 000  NULL
23Transaction1 0 000  NULL
24VerifyCookie   1 1 000  NULL
25TableLock  1 2 0 test   00  NULL
26Goto   0 10000  NULL

-Ursprüngliche Nachricht-
Von: Gabriel Corneanu [mailto:gabrielcorne...@gmail.com]
Gesendet: Montag, 01. Juli 2013 14:58
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] Is there a way to return the row number? (NOT therowid)

Then this register value is exactly the needed result.
There is also the other syntax, "limit n, m"; you have to skip somehow "m" rows.

Gabriel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


--
 Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna, Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then delete this message from 
your system. Please do not copy it or use it for any purposes, or disclose its 
contents to any person as to do so could be a breach of confidence. Thank you 
for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

[Gabriel Corneanu]

Simon,
As I wrote it's easy to do when you control the loop.
Sometimes there is either no explicit loop (direct sql, no programming 
code) or the loop is out of your reach (3rd party library).


Gabriel

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to return the row number? (NOT therowid)

[Gabriel Corneanu]

Then this register value is exactly the needed result.
There is also the other syntax, "limit n, m"; you have to skip somehow 
"m" rows.


Gabriel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting Constraints Details

[Kees Nuyt]

On Mon, 1 Jul 2013 07:23:54 +, Vijay Khurdiya
 wrote:

>How to get details of constraints associated with Data in SQLite3.
>
>Ex : I want to find out DEFAULT constraints value associated with data.

Some of that info is available in the results of
PRAGMA table_info(yourtablename);

-- 
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

[Simon Slavin]

On 1 Jul 2013, at 10:33am, Tony Papadimitriou  wrote:

> Is there a function (or method), e.g., row(), to return the sequence number 
> of the selected row?  This is not the same as ROWID.  row() should give a 
> sequence number always starting from 1 up the to the number of rows returned 
> by the view/select etc.

Nope.  It’s your software that calls _step() to get the next row.  So you can 
keep count of that number in a variable in your software.  There’s no advantage 
in having SQLite do it.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to return the row number? (NOT therowid)

[Hick Gunter]

For this application I would suggest:

DROP TABLE IF EXISTS query_results;
CREATE TEMP TABLE query_results (...);
INSERT INTO query_results
SELECT 
ORDER BY 
LIMIT ;

SELECT rowid,* from query_results;

DROP TABLE IF EXISTS query_results;

SQLite implicitly assigns numerical ascending rowids to a table.
You may also use INTEGER PRIMARY KEY to assign your own name to the rowid 
column.
And if you need to backreference to the original table you could also store 
that table's rowid (which may change in certain cases).

-Ursprüngliche Nachricht-
Von: Dave Wellman [mailto:dwell...@ward-analytics.com]
Gesendet: Montag, 01. Juli 2013 13:20
An: 'General Discussion of SQLite Database'
Betreff: Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

Hi,

Where the "row number in the answer set" does come in useful (or at least where 
I've used it a number of itmes) is when populating a table with the results of 
a select and including this value as another column in that table.

"build a table with the top 10 selling items over the last week"

Insert into target-table
Select productid
,sum(sales)
   ,row-number-in-answer-set as sales_rank
>From sales_table...

This involves a single sql request with no returning of data rows back to the 
application. Very easy to code and execute.

The above table can then be compared to an equivalent list of products from the 
previous week - this then forms the basis of a lot of useful analysis.

Cheers,
Dave

Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey, United 
Kingdom, GU1 3SR Registered company number: 3917021 Registered in England and 
Wales.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Alex Bowden
Sent: 01 July 2013 12:07
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Is there a way to return the row number? (NOT the
rowid)

> This would not be something you would sort by.

And what if I do?

> It should be assigned a value only during final 'display' of the query
after all 'sorts' of operations are done with.

Oh great.  So the user is supposed to understand the implementation, in order 
to understand what the results will be.


This would be just another nail in the coffin of relationality and simplicity, 
on a minor whim.

On 1 Jul 2013, at 11:01, Tony Papadimitriou  wrote:

>
> -Original Message- From: Alex Bowden
> Sent: Monday, July 01, 2013 12:46 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Is there a way to return the row number? (NOT
therowid)
>
>
> I can't wait to try
>
> order by row_number desc
>
>
> On 1 Jul 2013, at 10:33, Tony Papadimitriou  wrote:
>
>> Is there a function (or method), e.g., row(), to return the sequence
number of the selected row?  This is not the same as ROWID.  row() should give 
a sequence number always starting from 1 up the to the number of rows returned 
by the view/select etc.
>>
>> If not, then please add to the wish list.
>>
>> TIA
>> ___
>> 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
> ___
> 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

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


--
 Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna, Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then delete this message from 
your system. Please do not copy it or use it for any purposes, or disclose its 
contents to any person as to do so could be a breach of confidence. Thank you 
for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to return the row number? (NOT therowid)

[Tony Papadimitriou]

Thanks!  (At least some understand what I mean!)

And people giving examples of how it can be done in C (or Python, for me) or 
whatever language miss the point.  We're not talking how it can be 
programmatically.  This is easy!!!  How does one do it via pure SQL is the 
real question.


As for the temporary table idea, I had this one, too!  But it seems too much 
overhead for such a simple thing, so I was hoping for something like an 
internal symbol, or special function.


-Original Message- 
From: Gabriel Corneanu

Sent: Monday, July 01, 2013 2:10 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Is there a way to return the row number? (NOT 
therowid)


I also needed this for some special update queries.
Without many details, it was some kind of "insert into xxx select
, otherfields from source order by ".
For this case there is a workaround, selecting first into a temporary
table with auto generated rowid and using it afterwards for insert.

There are lots of other cases where this would be handy, e.g. showing
ordinal of some results.

... 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to return the row number? (NOTthe rowid)

[Tony Papadimitriou]

Please!  Just because you can select something doesn't mean you have to be 
able to sort by it.  Can you sort by *
(select * by table sort by *)?  So, why make it sound like I don't know what 
I'm talking about?


-Original Message- 
From: Alex Bowden

Sent: Monday, July 01, 2013 2:07 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Is there a way to return the row number? (NOTthe 
rowid)



This would not be something you would sort by.


And what if I do?

It should be assigned a value only during final 'display' of the query 
after all 'sorts' of operations are done with.


Oh great.  So the user is supposed to understand the implementation, in 
order to understand what the results will be.



This would be just another nail in the coffin of relationality and 
simplicity, on a minor whim.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

[Dave Wellman]

Hi,

Where the "row number in the answer set" does come in useful (or at least
where I've used it a number of itmes) is when populating a table with the
results of a select and including this value as another column in that
table.

"build a table with the top 10 selling items over the last week"

Insert into target-table
Select productid
,sum(sales)
   ,row-number-in-answer-set as sales_rank
>From sales_table...

This involves a single sql request with no returning of data rows back to
the application. Very easy to code and execute.

The above table can then be compared to an equivalent list of products from
the previous week - this then forms the basis of a lot of useful analysis.

Cheers,
Dave

Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR
Registered company number: 3917021 Registered in England and Wales.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Alex Bowden
Sent: 01 July 2013 12:07
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Is there a way to return the row number? (NOT the
rowid)

> This would not be something you would sort by.  

And what if I do?

> It should be assigned a value only during final 'display' of the query
after all 'sorts' of operations are done with.

Oh great.  So the user is supposed to understand the implementation, in
order to understand what the results will be.


This would be just another nail in the coffin of relationality and
simplicity, on a minor whim.

On 1 Jul 2013, at 11:01, Tony Papadimitriou  wrote:

> 
> -Original Message- From: Alex Bowden
> Sent: Monday, July 01, 2013 12:46 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Is there a way to return the row number? (NOT
therowid)
> 
> 
> I can't wait to try
> 
> order by row_number desc
> 
> 
> On 1 Jul 2013, at 10:33, Tony Papadimitriou  wrote:
> 
>> Is there a function (or method), e.g., row(), to return the sequence
number of the selected row?  This is not the same as ROWID.  row() should
give a sequence number always starting from 1 up the to the number of rows
returned by the view/select etc.
>> 
>> If not, then please add to the wish list.
>> 
>> TIA
>> ___
>> 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 
> ___
> 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

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

[Gabriel Corneanu]

I also needed this for some special update queries.
Without many details, it was some kind of "insert into xxx select 
, otherfields from source order by ".
For this case there is a workaround, selecting first into a temporary 
table with auto generated rowid and using it afterwards for insert.


There are lots of other cases where this would be handy, e.g. showing 
ordinal of some results.
Of course it is simple to implement when the loop is under your control. 
But sometimes there are libraries (components) which are interfaced only 
by a cursor.
E.g a listing/grid showing cursor results, where you can NOT control the 
implementation but want to show "row" as a column.


I currently use another workaround for this, declaring a user function 
which simply increments/returns a counter.
As long as the queries are simple it is ok, but wrong usage is easily 
possible (e.g. multiple times per row).


As the sqlite query program already has an internal loop, it would be 
very easy to increment a counter for each "next" opcode and return it 
via a special function or pseudo column.
I believe such a counter already exists for "LIMIT" clause, so most of 
the work is already done...


Regards,
Gabriel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

[Alex Bowden]

> This would not be something you would sort by.  

And what if I do?

> It should be assigned a value only during final 'display' of the query after 
> all 'sorts' of operations are done with.

Oh great.  So the user is supposed to understand the implementation, in order 
to understand what the results will be.


This would be just another nail in the coffin of relationality and simplicity, 
on a minor whim.

On 1 Jul 2013, at 11:01, Tony Papadimitriou  wrote:

> 
> -Original Message- From: Alex Bowden
> Sent: Monday, July 01, 2013 12:46 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Is there a way to return the row number? (NOT therowid)
> 
> 
> I can't wait to try
> 
> order by row_number desc
> 
> 
> On 1 Jul 2013, at 10:33, Tony Papadimitriou  wrote:
> 
>> Is there a function (or method), e.g., row(), to return the sequence number 
>> of the selected row?  This is not the same as ROWID.  row() should give a 
>> sequence number always starting from 1 up the to the number of rows returned 
>> by the view/select etc.
>> 
>> If not, then please add to the wish list.
>> 
>> TIA
>> ___
>> 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 
> ___
> 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] Is there a way to return the row number? (NOT the rowid)

[RSmith]

I can't wait to try

order by row_number desc


LOL - But done already:
  PRAGMA reverse_unordered_selects = 1;


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

[RSmith]

I can't imagine the usefulness of this. The SQLite will have to run a query to find it, same as you need to, consider some standard 
sqlite code:


sqlite prepare
n=0
while sqlite_next=SQLITE_OK do {
  // do something with data
  inc(n)
}

The physical index of the row inside the query will always be given by n
If you want the physical index of the row in the database, simply get a 
non-ordered query ex:
  Select Id from t where 1
and check n for any returned Id.  (The "where 1" bit is superfluous I know, but 
only serves the explanation)

If you were thinking of having the rownum available as a quick reference id, like rowid, I can't see how this will be achieved 
wthout making another index out of it, something I personally wouldn't like to see. Also this will change with deletions etc, so 
will have high maintenance even in Index form.



On 2013/07/01 11:33, Tony Papadimitriou wrote:
Is there a function (or method), e.g., row(), to return the sequence number of the selected row?  This is not the same as ROWID.  
row() should give a sequence number always starting from 1 up the to the number of rows returned by the view/select etc.


If not, then please add to the wish list.

TIA


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

[Tony Papadimitriou]

This would not be something you would sort by.  It should be assigned a 
value only during final 'display' of the query after all 'sorts' of 
operations are done with.


-Original Message- 
From: Alex Bowden

Sent: Monday, July 01, 2013 12:46 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Is there a way to return the row number? (NOT 
therowid)



I can't wait to try

order by row_number desc


On 1 Jul 2013, at 10:33, Tony Papadimitriou  wrote:

Is there a function (or method), e.g., row(), to return the sequence 
number of the selected row?  This is not the same as ROWID.  row() should 
give a sequence number always starting from 1 up the to the number of rows 
returned by the view/select etc.


If not, then please add to the wish list.

TIA
___
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 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

[Alex Bowden]

I can't wait to try

order by row_number desc


On 1 Jul 2013, at 10:33, Tony Papadimitriou  wrote:

> Is there a function (or method), e.g., row(), to return the sequence number 
> of the selected row?  This is not the same as ROWID.  row() should give a 
> sequence number always starting from 1 up the to the number of rows returned 
> by the view/select etc.
> 
> If not, then please add to the wish list.
> 
> TIA 
> ___
> 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

[sqlite] Is there a way to return the row number? (NOT the rowid)

[Tony Papadimitriou]

Is there a function (or method), e.g., row(), to return the sequence number 
of the selected row?  This is not the same as ROWID.  row() should give a 
sequence number always starting from 1 up the to the number of rows returned 
by the view/select etc.


If not, then please add to the wish list.

TIA 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Another 2 questions about SQLite

[RSmith]

I wish.
I need to remove those records from another table as well. That's why I
need to retrieve playerid first.
So once again: will the delete affect the outer looping SQLite statement?


I hate to be the one asking the obvious questions, but why are you not using a trigger or or a foreign key to handle this?  Unless 
your "other table" is also in another database, which I doubt due to your other question, then you can still set up a code call-back 
to your own program to do the linked deletion, but as the other Igor noted, I think you are trying to overcomplicate things.


If you can give us a more detailed view of your database and exactly how and when linked fields need to be 
deleted/updated/whatever,  we'd be able to suggest an uncomplicated clear Foreign key relationship or Trigger method that should 
achieve it with the maximum efficiency.


Have a great day!




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Final preparations for the release of System.Data.SQLite v1.0.87.0 have begun...

[Joe Mistachkin]

If you have any issues with the current code, please report them via this
mailing
list (and/or by creating a ticket on "https://system.data.sqlite.org/;)
prior to
next Monday, July 8th.

The support for implementing virtual tables in managed code is completely
new and
merits special attention.

Thanks.

--
Joe Mistachkin

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting Constraints Details

[Clemens Ladisch]

Vijay Khurdiya wrote:
> How to get details of constraints associated with Data in SQLite3.

There is no API to get individual properties; you have to look up the
original SQL statement:

SELECT sql FROM sqlite_master WHERE type = 'table' AND name = 'MyTable'

> This e-mail and any files transmitted with it are for the sole use ...

This e-mail contains public information intended for any subscriber of
this mailing list and for anybody else who bothers to read it; it will
be copied, disclosed and distributed to the public.  If you think you
are not the intended recipient, please commit suicide immediately.
These terms apply also to any e-mails quoted in, referenced from, or
answering this e-mail, and supersede any confidentiality notices in
those e-mails.  Additionally, confidentiality notices in those e-mails
will incur legal processing fees of $42 per line; you have agreed to
this by reading this confidentiality notice.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Getting Constraints Details

[Vijay Khurdiya]

How to get details of constraints associated with Data in SQLite3.

Ex : I want to find out DEFAULT constraints value associated with data.




This e-mail and any files transmitted with it are for the sole use of the 
intended recipient(s) and may contain confidential and privileged information. 
If you are not the intended recipient, please contact the sender by reply 
e-mail and destroy all copies and the original message. Any unauthorized 
review, use, disclosure, dissemination, forwarding, printing or copying of this 
email or any action taken in reliance on this e-mail is strictly prohibited and 
may be unlawful. The recipient acknowledges that Secure Meters Limited or its 
subsidiaries and associated companies(collectively "Secure Meters Limited"),are 
unable to exercise control or ensure or guarantee the integrity of/over the 
contents of the information contained in e-mail transmissions and further 
acknowledges that any views expressed in this message are those of the 
individual sender and no binding nature of the message shall be implied or 
assumed unless the sender does so expressly with due authority of Secure Meters 
Limi
 ted. Before opening any attachments please check them for viruses and defects. 
In case you have any problem or issue with the E-mails from Secure Meters 
Limited Please do lets us know on netad...@securetogether.com Secure Meters 
Limited- Udaipur- Rajasthan -313001
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users