lundi 2 mai 2016

SQLite: Number rows of query output

I am learning SQLite and I am currently posing the question whether there is a simple way of adding a sequential numbering to the output of a query. Underneath, I provide an example of what I am trying to achieve.

For instance, I have the following query:

    splTicker AS 'Ticker',
    count(splTicker) AS '# of Splits'
FROM Splits
GROUP BY splTicker
ORDER BY count(splTicker) DESC, splTicker ASC;

The output of this query is as follows:

bash-3.2$ sqlite3 myShares < Queries/Split.sql
Ticker      # of Splits
----------  -----------
AI.PA       7          
ASML.AS     3          
BN.PA       3          
ALTR.LS     2          
BOKA.AS     2          
DG.PA       2 
SON.LS      1          
SU.PA       1          
SW.PA       1          
TEC.PA      1          
UMI.BR      1          
VIV.PA      1          
VPK.AS      1 

I am trying to add a sequential number to the rows to obtain the following output:

#  Ticker      # of Splits
-- ----------  -----------
1  AI.PA       7          
2  ASML.AS     3          
3  BN.PA       3          
4  ALTR.LS     2          
5  BOKA.AS     2          
6  DG.PA       2

Currently, I use a workaround and add the row numbers post-query in Perl. I am posing the question whether I could do this directly in SQLite. The idea seems simple, but I have not found a solution yet. Any help would be appreciated.

Best regards,


Aucun commentaire:

Enregistrer un commentaire