Providing incrementing column to query

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Providing incrementing column to query

Paul Sanderson-2
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 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:[hidden email]]
> 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 <[hidden email]> 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
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 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 <[hidden email]> 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:[hidden email]]
> > 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 <[hidden email]> 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
> > > [hidden email]
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Providing incrementing column to query

Jean-Luc Hainaut
In reply to this post by Paul Sanderson-2

Some suggestions, the validity of which depend on the context and
whether some ordering must be preserved.
Let T(C1 primary key,other_data) be the table with which we want to
associate a sequence number (as pseudo column Seq).

Technique 1: Computing Seq through a recursive CTE.

create table T0 as
with SEQUENCE(Seq,C1,other_data) as
      (select 1,C1,other_data
       from (select C1,other_data
             from   T order by C1 limit 1)
                union
             select S.Seq+1,T.C1,T.other_data
             from   T, SEQUENCES
             where  T.C1 = (select min(C1)
                            from   T
                            where  C1 > S.C1)
       )
select * from SEQUENCE;

Technique 2: Extracting rowid from source table (unordered)

create temp table T1(Seq integer,C1,other_data);
insert into T1
    select rowid as Seq,* from T order by C1;

Technique 3: Extracting rowid from a temp table (ordered)

create temp table T2as
    select 0 as Seq,* from T order by C1;
update T2 set Seq = rowid;

Technique 4: Adding auto incremented column in a temp table

create temp table T3(Seq integer primary key autoincrement,C1,other_data)
insert into T3(C1,other_data) select * from Torder by C1;

Technique 5: From declarative definition - suggestion of (C. Ladish)

create temp table T4as
select (select count(*)
         from   TT2
         where  T2.C1 <= T1.C1) as Seq,
         C1,
         other_data
from T T1 order by C1;

For small tables (10-100), the execution times are similar (around 1
ms.), but for medium-size to large tables, some techniques are better.
For a source table T of 32,000 rows of 30 bytes, there are clear winners:
   - technique 1: 185 ms.
- technique 2: 24 ms.
- technique 3: 58 ms.
- technique 4: 17 ms.
- technique 5: 42,000 ms.

All the scores are linear wrt table size but the declarative one, which
is quadratic.

Regards

Jean-Luc Hainaut





_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...