Displaying row count

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Displaying row count

David Fletcher
Hi all,

Is there a mode in the sqlite shell, or some fancy extension, that will
display a row
number when outputting results?  You know, something like this:

     sqlite> .row on
     sqlite> select * from SomeTable where ... ;
     1. a|17|93|...
     2. b|212|104|...

I tend to use the sqlite shell for debugging new queries and seeing a row
number would save me from always doing a 'select count(*) from (...
previous select ...)'
command.

Thanks,

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

Re: Displaying row count

Clemens Ladisch
David Fletcher wrote:> Hi all,
> Is there a mode in the sqlite shell, or some fancy extension, that will display a row
> number when outputting results?

No.  You'd have to modify the shell, or add the row_number() window function to the
query.


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
|

Re: Displaying row count

Dominique Devienne
On Wed, Oct 31, 2018 at 3:55 PM Clemens Ladisch <[hidden email]> wrote:

> David Fletcher wrote:> Hi all,
> > Is there a mode in the sqlite shell, or some fancy extension, that will
> display a row
> > number when outputting results?
>
> No.  You'd have to modify the shell, or add the row_number() window
> function to the query.


Unless you need to use bleeding edge new features, use some GUI SQLite tool
instead.

I use SQliteSpy myself for that. Gives me the time to process the query,
how many rows,
how many VM steps, Sort steps, that sort of thing. And shows me the result
in tabular fashion,
with color-coding based on value types (useful for a ducktyping DB like
SQLite).

Windows only, very simple, but very fast. Uses SQLite 3.21, but updated
once in a while.

I typically keep several queries I'm playing with, and CTRL-F9 the selected
one to run it.

Don't get me wrong, the CLI shell is great, but for many rows, or wide rows,
or plain convenience, a GUI tool is often more practical IMHO. My $0.02.
--DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Displaying row count

Don V Nielsen
I really enjoy using JetBrains DataGrip. It connects to everything and has
great intellisense, find and replace tools, sql templates, all the goodies
a big IDE brings to the table.

On Wed, Oct 31, 2018 at 11:05 AM Dominique Devienne <[hidden email]>
wrote:

> On Wed, Oct 31, 2018 at 3:55 PM Clemens Ladisch <[hidden email]>
> wrote:
>
> > David Fletcher wrote:> Hi all,
> > > Is there a mode in the sqlite shell, or some fancy extension, that will
> > display a row
> > > number when outputting results?
> >
> > No.  You'd have to modify the shell, or add the row_number() window
> > function to the query.
>
>
> Unless you need to use bleeding edge new features, use some GUI SQLite tool
> instead.
>
> I use SQliteSpy myself for that. Gives me the time to process the query,
> how many rows,
> how many VM steps, Sort steps, that sort of thing. And shows me the result
> in tabular fashion,
> with color-coding based on value types (useful for a ducktyping DB like
> SQLite).
>
> Windows only, very simple, but very fast. Uses SQLite 3.21, but updated
> once in a while.
>
> I typically keep several queries I'm playing with, and CTRL-F9 the selected
> one to run it.
>
> Don't get me wrong, the CLI shell is great, but for many rows, or wide
> rows,
> or plain convenience, a GUI tool is often more practical IMHO. My $0.02.
> --DD
> _______________________________________________
> 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
|

Re: Displaying row count

Peter da Silva-2
If you're going to change the sqlite3 command shell, add a ".count" and
maybe ".changes" display option.

On Wed, Oct 31, 2018 at 11:23 AM Don V Nielsen <[hidden email]>
wrote:

> I really enjoy using JetBrains DataGrip. It connects to everything and has
> great intellisense, find and replace tools, sql templates, all the goodies
> a big IDE brings to the table.
>
> On Wed, Oct 31, 2018 at 11:05 AM Dominique Devienne <[hidden email]>
> wrote:
>
> > On Wed, Oct 31, 2018 at 3:55 PM Clemens Ladisch <[hidden email]>
> > wrote:
> >
> > > David Fletcher wrote:> Hi all,
> > > > Is there a mode in the sqlite shell, or some fancy extension, that
> will
> > > display a row
> > > > number when outputting results?
> > >
> > > No.  You'd have to modify the shell, or add the row_number() window
> > > function to the query.
> >
> >
> > Unless you need to use bleeding edge new features, use some GUI SQLite
> tool
> > instead.
> >
> > I use SQliteSpy myself for that. Gives me the time to process the query,
> > how many rows,
> > how many VM steps, Sort steps, that sort of thing. And shows me the
> result
> > in tabular fashion,
> > with color-coding based on value types (useful for a ducktyping DB like
> > SQLite).
> >
> > Windows only, very simple, but very fast. Uses SQLite 3.21, but updated
> > once in a while.
> >
> > I typically keep several queries I'm playing with, and CTRL-F9 the
> selected
> > one to run it.
> >
> > Don't get me wrong, the CLI shell is great, but for many rows, or wide
> > rows,
> > or plain convenience, a GUI tool is often more practical IMHO. My $0.02.
> > --DD
> > _______________________________________________
> > 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
|

Re: Displaying row count

Chris Locke-3
In reply to this post by David Fletcher
>  that will display a row number when outputting results?

Is this for your schema, or a 'general-could-be-anything' schema?  If your
own, any reason why you don't use the rowid or _rowid_ columns?  They
provide a unique reference for each row in a table.


Thanks,
Chris


On Wed, Oct 31, 2018 at 2:54 PM David Fletcher <[hidden email]> wrote:

> Hi all,
>
> Is there a mode in the sqlite shell, or some fancy extension, that will
> display a row
> number when outputting results?  You know, something like this:
>
>      sqlite> .row on
>      sqlite> select * from SomeTable where ... ;
>      1. a|17|93|...
>      2. b|212|104|...
>
> I tend to use the sqlite shell for debugging new queries and seeing a row
> number would save me from always doing a 'select count(*) from (...
> previous select ...)'
> command.
>
> Thanks,
>
> David
> _______________________________________________
> 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
|

Re: Displaying row count

R Smith-2
In reply to this post by David Fletcher
On 2018/10/31 4:52 PM, David Fletcher wrote:

> Hi all,
>
> Is there a mode in the sqlite shell, or some fancy extension, that
> will display a row
> number when outputting results?  You know, something like this:
>
>     sqlite> .row on
>     sqlite> select * from SomeTable where ... ;
>     1. a|17|93|...
>     2. b|212|104|...

Well you're in luck, SQLite has just recently adopted the great
Windowing functions addition which provides a way to achieve this.
(Note: this will only work from sqlite version 3.25 onward)

Simply add a column to any select like this: "row_number() OVER
(partition by 1)", et voila...

Example:
   -- SQLite version 3.25.1  [ Release: 2018-09-18 ]  on SQLitespeed
version 2.1.1.37.
   --
================================================================================================
CREATE TABLE t(a,b);

INSERT INTO t VALUES
  (1,'AAA')
,(6,'BBB')
,(2,'CCC')
,(4,'DDD')
;

SELECT row_number() OVER (PARTITION BY 1) No, *
   FROM t
;
   --      No      |       a      |  b
   -- ------------ | ------------ | ---
   --       1      |       1      | AAA
   --       2      |       2      | CCC
   --       3      |       4      | DDD
   --       4      |       6      | BBB


You can get further creative by adding an ORDER BY clause inside the
window function if you like to have the same row number in a repeatable
order on subsequent queries.
See here:
https://www.sqlite.org/windowfunctions.html#introduction_to_window_functions

Or, as others suggested, simply use a GUI - in https://sqlitespeed.com 
as an example, the query has a simple setting switching row numbering on
and off - but it is blind to order, it will number any row the DB engine
spits out in the order it is spat out. Use the Windowing functions if
you need a repeatable/reference-able solution or one that will work in
the CLI.


Good luck!
Ryan

>
> I tend to use the sqlite shell for debugging new queries and seeing a row
> number would save me from always doing a 'select count(*) from (...
> previous select ...)'
> command.
>
> Thanks,
>
> David
> _______________________________________________
> 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
|

Re: Displaying row count

Nathan Green
My typical use case is trying to determine how many rows I just wrote out
to a file. Window functions are no help because they alter the output
format, which is usually undesirable. It's sort of absurd to run `wc -l`
over the newest 25MiB file in my system to get an answer that's probably
already in RAM.

Nathan

On Wed, Oct 31, 2018 at 12:27 PM R Smith <[hidden email]> wrote:

> On 2018/10/31 4:52 PM, David Fletcher wrote:
> > Hi all,
> >
> > Is there a mode in the sqlite shell, or some fancy extension, that
> > will display a row
> > number when outputting results?  You know, something like this:
> >
> >     sqlite> .row on
> >     sqlite> select * from SomeTable where ... ;
> >     1. a|17|93|...
> >     2. b|212|104|...
>
> Well you're in luck, SQLite has just recently adopted the great
> Windowing functions addition which provides a way to achieve this.
> (Note: this will only work from sqlite version 3.25 onward)
>
> Simply add a column to any select like this: "row_number() OVER
> (partition by 1)", et voila...
>
> Example:
>    -- SQLite version 3.25.1  [ Release: 2018-09-18 ]  on SQLitespeed
> version 2.1.1.37.
>    --
>
> ================================================================================================
> CREATE TABLE t(a,b);
>
> INSERT INTO t VALUES
>   (1,'AAA')
> ,(6,'BBB')
> ,(2,'CCC')
> ,(4,'DDD')
> ;
>
> SELECT row_number() OVER (PARTITION BY 1) No, *
>    FROM t
> ;
>    --      No      |       a      |  b
>    -- ------------ | ------------ | ---
>    --       1      |       1      | AAA
>    --       2      |       2      | CCC
>    --       3      |       4      | DDD
>    --       4      |       6      | BBB
>
>
> You can get further creative by adding an ORDER BY clause inside the
> window function if you like to have the same row number in a repeatable
> order on subsequent queries.
> See here:
>
> https://www.sqlite.org/windowfunctions.html#introduction_to_window_functions
>
> Or, as others suggested, simply use a GUI - in https://sqlitespeed.com
> as an example, the query has a simple setting switching row numbering on
> and off - but it is blind to order, it will number any row the DB engine
> spits out in the order it is spat out. Use the Windowing functions if
> you need a repeatable/reference-able solution or one that will work in
> the CLI.
>
>
> Good luck!
> Ryan
>
> >
> > I tend to use the sqlite shell for debugging new queries and seeing a row
> > number would save me from always doing a 'select count(*) from (...
> > previous select ...)'
> > command.
> >
> > Thanks,
> >
> > David
> > _______________________________________________
> > 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
|

Re: Displaying row count

E.Pasma
In reply to this post by R Smith-2

> R Smith:
>
> Simply add a column to any select like this: et voila...


I tried without  "partition by 1" and that works as well: "row_number() OVER ()"


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