CODEWITHSUNDEEP
sqlsqlite
Alan2
Alan2

Reputation: 24592

Is there a way I can get a row/seq number in the output of a select with SQLite?

I have this SQL:

SELECT English, F1, F3, F3, F4, F5, Hidden, PhraseId FROM Phrase

What I would like to do is to return in addition to this a number for each record selected.

So for example the query would return:

1  f1result f2result f3result etc
2  f1result f2result f3result etc

Upvotes: 0

Views: 44

Answers (2)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

you can use row_number() like below if your version support

SELECT 
 row_number() overr(order by (select null)) rn
,English, F1, F3, F3, F4, F5, Hidden, PhraseId FROM Phrase

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270873

In the more recent versions of SQLite, you can use row_number():

SELECT row_number() over (order by English) as seqnum
       English, F1, F3, F3, F4, F5, Hidden, PhraseId
FROM Phrase;

In older versions, this is much harder, requiring a correlated subquery -- and it will not work well on larger amounts of data.

Upvotes: 3

Related Questions