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
