Search the position of a column in SQLite

Question

I am searching... and I don't find how can I find in SQlite what is the position of an item in the column.

When i use ROW_NUMBER() i get:

ERROR: near "(": syntax error (code 1)

SELECT Nom ROW_NUMBER () OVER (ORDER BY Score) AS Rownumber 
FROM tableau

I'm using MIT App Inventor with Taifun extension sqlite

Other question how to know which item is in position 2 (or another number) in the column?

At least, you should post your query here.Anw, Are u sure that the version of sql server which u are using , does it support ROW_NUMBER — tauitdnmd, Sep 15 '17 at 09:55
Please provide the SQL you have attempted and some sample data — Mr Slim, Sep 15 '17 at 10:02
How to post my database? It's included in my app, the database is empty at the beginning and the app add column and index... — clement pignet, Sep 15 '17 at 10:21
SQLite does not support `row_number()`. Do some investigation. If that doesn't answer your question, then ask another question. — Gordon Linoff, Sep 15 '17 at 10:26
What about using `ROWID`? see also https://stackoverflow.com/a/12235189/1545993 ... you might want to do some SQLite tutorials... — Taifun, Sep 15 '17 at 14:38

score 0 · Answer 1 · edited Sep 15 '17 at 10:29

0

This is too long for a comment.

ROW_NUMBER() is an ANSI-standard function. However, not all databases use it. For instance, SQLite, MySQL, and MS Access (among others) do not support this functionality.

Presumably, you are using one of these database that does not support this function.

I would suggest that you research what database you are using. Then, try investigating how to implement the functionality you want using that database. If you can't figure out how, ask another question, providing sample data, desired results, and a tag for the database you are using.

edited Sep 15 '17 at 10:29

Matt Gibson

37,886
9
99
128

answered Sep 15 '17 at 10:22

Gordon Linoff

1,242,037
58
646
786

I know now I use sqlite ! – clement pignet Sep 15 '17 at 10:25

score 0 · Answer 2 · answered Sep 15 '17 at 10:32

If this is indeed the code you are running:

SELECT Nom ROW_NUMBER () OVER (ORDER BY Score) AS Rownumber FROM tableau

Then what is Nom? This is a syntax error in practically every implementation. What you are probably looking to do is:

SELECT Nom, ROW_NUMBER () OVER (ORDER BY Score) AS Rownumber FROM tableau

Notice the comma after Nom.

Search the position of a column in SQLite

2 Answers2