SQLite Forum

Select by column id

Select by column id

(1) By anonymous on 2020-08-11 08:59:43

Hi everyone, I'm new to the SQLite, there's one thing I am wondering, after tons of researches on google and youtube, still can't find the answer.

So my question is, can I select by calling the column id like the primary key, the rowid? Or can I select it by calling a variable that stored the name of the column? 

Thank you all, any reply will be appreciated!

(2) By John Dennis (jdennis) on 2020-08-11 12:07:47 in reply to 1 [link]

Not certain whether this answers your question, but it is perfectly valid to do a select from a table where rowid=9999

The difficulty is knowing the rowid, which may change if the table is reloaded.

(3) By David Raymond (dvdraymond) on 2020-08-11 12:13:14 in reply to 1 [link]

As far as I know, no. There is no way that I'm aware of to say...

SELECT "the 4th column, whatever its name is" FROM table1;

You could SELECT *, and then grab the 4th column, but you can't just say "the 4th column".

If you have the column name in a variable (or table name for that matter) you'll have to use string manipulation to insert it into the SQL text. Binding only works for values. So you'll have to do something like

sql = "select " + appropriately_quote(column_name) + " from table1;"

Where appropriately_quote(column_name) is you making sure it's quoted correctly if needed. Some libraries provide tools for that, others may not. But that's where you prevent injection, handle spaces in the column name or other weird things, etc.

(I was gonna add "others please correct me if I'm wrong," but then remembered this is the internet and that's gonna happen anyway)

(4) By Gunter Hick (gunter_hick) on 2020-08-11 16:09:35 in reply to 1 [link]

Note that the order of columns may be changed in either the declaration of a table or the field list in the select statement. It is considered quite risky to rely on any implicit order of fields in a query.

With the C interface, the sqlite3_column() family of functions refer to the nth column of the result by providing the desired index in the iCol parameter.

There is no syntax for SELECT <column #7> FROM table other than providing the name of the seventh column in the statement itself. Neither is it possible to SELECT :col FROM table or to SELECT <column> FROM :table because the names of the coulmns and tables must be known beforehand to allow a query to be prepared.

(5) By Richard Damon (RichardDamon) on 2020-08-11 16:10:36 in reply to 1 [link]

Your question is a bit confusing and I am not sure if people know just what you are asking, If you want to be able to name the column that will be the rowid, then when you declare the table, just make the column INTEGER PRIMARY KEY, and the name of the column can be used to access the primary key, as well as SQLite will not change by table manipulations (unless you directly change it). Note that a column so declared acts a bit differently than other integer columns, in that it can't be NULL or store a value that isn't an integer.

If you don't do this, then you can reference the rowid by several different names (like ROWID) assuming you haven't named some other column by that name. As was mentioned, one issue with this is that some operations can change this value, so you shouldn't be storing this number somewhere to find the record (the big advantage of declaring a column to be the INTEGER PRIMARY KEY.

(6.2) By Keith Medcalf (kmedcalf) on 2020-08-11 20:59:27 edited from 6.1 in reply to 5 [link]

You can *always* refer to the ROWID of a ROWID Table using the names `ROWID` or `_rowid_` unless those names have been otherwise declared to not be the rowid, whether or not the rowid (integer primary key) as been given another name.

For example, in the following:

create table x
  x integer primary key,
  w integer

The queries return exactly the same result (save the column name):

select * from x;
select x, w from x;
select rowid, w from x;
select _rowid_, w from x;
select * from x where x == _rowid_;
select * from x where x == rowid;
select * from x where rowid == _rowid_;

(7) By anonymous on 2022-07-31 04:25:09 in reply to 6.2 [link]


(8) By Chris Locke (chrisjlocke1) on 2022-07-31 08:00:33 in reply to 1 [link]

There is an analogy of someone saying, "I need to get cross this river.  I've tried building a boat, a raft, and different designs.  What is the best boat to cross this river?".  However, you only want to cross the river as you want to get to London.  There is a simple path over there that leads to London.

Weird way of saying, instead of asking to solve solution X, explain problem Y.  There might be solution Z.

Why are you trying to call a column ID from a variable?  What program language are you programming in?  (Not that that greatly matters, but I'm interested and nosey).