Re: [sqlite] Providing incrementing column to query

[Paul Sanderson]

Actually I just need a unique number - but sorted in code now.

Thank You

Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

On 24 June 2017 at 15:57, Keith Medcalf  wrote:

>
> Of course, if the traversal order is different than the row return order
> then you will not get ascending logical row numbers unless you do something
> like:
>
> select logicalrow() as SequenceNumber,
>t.*
>   from (...query ...) as t;
>
> If you need logical row numbers it is much better (and far more efficient)
> to assign them in your program as the results are returned.
>
> Out of curiosity, why do you need logical result row numbers since they do
> not correlate with anything meaningful?
>
> --
> ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
>
> > -Original Message-
> > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> > On Behalf Of Paul Sanderson
> > Sent: Saturday, 24 June, 2017 06:18
> > To: SQLite mailing list
> > Subject: Re: [sqlite] Providing incrementing column to query
> >
> > Hmm thanks Clemens
> >
> > Have written an extension to do it - some of my tables are very big and
> > feel that the extension might be a better route.
> >
> > Paul
> > www.sandersonforensics.com
> > skype: r3scue193
> > twitter: @sandersonforens
> > Tel +44 (0)1326 572786
> > http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-
> > Toolkit
> > -Forensic Toolkit for SQLite
> > email from a work address for a fully functional demo licence
> >
> > On 24 June 2017 at 13:10, Clemens Ladisch  wrote:
> >
> > > Paul Sanderson wrote:
> > > > I Have a number of queries to which I want to supply an incrementing
> > > column,
> > > > some of these queries involve without rowid tables.
> > > >
> > > > Is there a way of doing this with a SQL query?
> > >
> > > First, define a sort order, and ensure that it does not have
> duplicates.
> > > Then use a correlated subquery to count how many rows would come before
> > > the current one in that order:
> > >
> > >   SELECT (SELECT count(*)
> > >   FROM MyTable AS T2
> > >   WHERE T2.name <= MyTable.Name
> > >  ) AS row_number,
> > >  name,
> > >  age
> > >   FROM MyTable
> > >   ORDER BY name;
> > >
> > > It would be a better idea to count returned rows in your program.
> > >
> > >
> > > Regards,
> > > Clemens
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@mailinglists.sqlite.org
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Providing incrementing column to query

[Keith Medcalf]

Of course, if the traversal order is different than the row return order then 
you will not get ascending logical row numbers unless you do something like:

select logicalrow() as SequenceNumber, 
   t.*
  from (...query ...) as t;

If you need logical row numbers it is much better (and far more efficient) to 
assign them in your program as the results are returned.

Out of curiosity, why do you need logical result row numbers since they do not 
correlate with anything meaningful?

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Paul Sanderson
> Sent: Saturday, 24 June, 2017 06:18
> To: SQLite mailing list
> Subject: Re: [sqlite] Providing incrementing column to query
> 
> Hmm thanks Clemens
> 
> Have written an extension to do it - some of my tables are very big and
> feel that the extension might be a better route.
> 
> Paul
> www.sandersonforensics.com
> skype: r3scue193
> twitter: @sandersonforens
> Tel +44 (0)1326 572786
> http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-
> Toolkit
> -Forensic Toolkit for SQLite
> email from a work address for a fully functional demo licence
> 
> On 24 June 2017 at 13:10, Clemens Ladisch  wrote:
> 
> > Paul Sanderson wrote:
> > > I Have a number of queries to which I want to supply an incrementing
> > column,
> > > some of these queries involve without rowid tables.
> > >
> > > Is there a way of doing this with a SQL query?
> >
> > First, define a sort order, and ensure that it does not have duplicates.
> > Then use a correlated subquery to count how many rows would come before
> > the current one in that order:
> >
> >   SELECT (SELECT count(*)
> >   FROM MyTable AS T2
> >   WHERE T2.name <= MyTable.Name
> >  ) AS row_number,
> >  name,
> >  age
> >   FROM MyTable
> >   ORDER BY name;
> >
> > It would be a better idea to count returned rows in your program.
> >
> >
> > Regards,
> > Clemens
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Providing incrementing column to query

[Paul Sanderson]

Hmm thanks Clemens

Have written an extension to do it - some of my tables are very big and
feel that the extension might be a better route.

Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

On 24 June 2017 at 13:10, Clemens Ladisch  wrote:

> Paul Sanderson wrote:
> > I Have a number of queries to which I want to supply an incrementing
> column,
> > some of these queries involve without rowid tables.
> >
> > Is there a way of doing this with a SQL query?
>
> First, define a sort order, and ensure that it does not have duplicates.
> Then use a correlated subquery to count how many rows would come before
> the current one in that order:
>
>   SELECT (SELECT count(*)
>   FROM MyTable AS T2
>   WHERE T2.name <= MyTable.Name
>  ) AS row_number,
>  name,
>  age
>   FROM MyTable
>   ORDER BY name;
>
> It would be a better idea to count returned rows in your program.
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Providing incrementing column to query

[Clemens Ladisch]

Paul Sanderson wrote:
> I Have a number of queries to which I want to supply an incrementing column,
> some of these queries involve without rowid tables.
>
> Is there a way of doing this with a SQL query?

First, define a sort order, and ensure that it does not have duplicates.
Then use a correlated subquery to count how many rows would come before
the current one in that order:

  SELECT (SELECT count(*)
  FROM MyTable AS T2
  WHERE T2.name <= MyTable.Name
 ) AS row_number,
 name,
 age
  FROM MyTable
  ORDER BY name;

It would be a better idea to count returned rows in your program.


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

Re: [sqlite] sqlite3 interactive shell failed assertions and segmentation faults

[Richard Hipp]

Thanks for the report.

Thanks to Simon for verifying that these are all associated with the
command-line shell only and not with the SQLite core.

Note to Ryan:  Please make sure your fuzzer  is running inside a
sandbox, in case the fuzzer discovers pernicious dot-commands like
".sy rm -rf ~"

On 6/23/17, Ryan Whitworth  wrote:
> Hello all,
>
> I was using American Fuzzy Lop (afl-fuzz) to fuzz test stdin to the sqlite3
> interactive shell.  AFL found a few inputs that cause segmentation faults
> (mostly due to failed assertions, I think?).  Is this sort of thing worth
> investigating further or a non-issue?
>
> GDB backtrace details and input files can be found here:
> https://github.com/rwhitworth/sqlite-fuzz/tree/master/2017-06-23-sqlite3.
> Tests can be re-run via 'sqlite3 -bail < id_filename'
>
> These inputs were found using a tarball download of the source from
> 2017-05-31 and also reconfirmed against a download on 2017-06-23.
>
> Thanks for your time,
> Ryan Whitworth
> m...@ryanwhitworth.com
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


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

[sqlite] Providing incrementing column to query

[Paul Sanderson]

I Have a number of queries to which I want to supply an incrementing column,
some of these queries involve without rowid tables.

 

I have no control over the design of the tables.

 

So for a table defined as: 

 

CREATE TABLE (name text, age integer) with values

 

Steve, 34

Eric, 27

Denis, 41

 

I want to return a result set

 

1, Steve, 34

2, Eric, 27

3, Denis, 41

 

 

Is there a way of doing this with a SQL query?

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