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 <ryansmit...@gmail.com> 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 > > 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
