• Welcome, Guest. Please login.
 
August 19, 2019, 07:28:07 am

News:

Welcome to the SQLitening support forums!


Retrieve A particular Item From A Field.

Started by Fredrick Ughimi, January 06, 2013, 06:16:25 pm

Previous topic - Next topic

Fredrick Ughimi

Hello,

With SQLitening how can one retrieve a particular value from a field. Lets say you these values in Field Age:

20  30  45  34  52

How can one retrieve only the third item (value 45) ? 
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Bern Ertl

Your question is not clear.

Do you have a column named FieldAge with separate records containing the values 20, 30, 45, 34, 52 or do you have a single record containing a string "20 30 45 34 52"?

If the latter, you need to use some string functions like PARSE$().

If the former, please show the table name and column name(s) that you want to retrieve and search criteria and I can help you with the correct SELECT statement.


Fredrick Ughimi

I meant retrieving items by the item position in a field. For example Item 3 above is 45.
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fred Meier

This would get the third lowest age   Select Age from MyTable Order by Age Limit 1 Offset 2

This would do the same thing   Select Age from MyTable Order by Age Limit 2,1

From SQLite doc:
QuoteThe LIMIT clause is used to place an upper bound on the number of rows
returned by a SELECT statement.  Any scalar expression may be used in the
LIMIT clause, so long as it evaluates to an integer or a value that can be
losslessly converted to an integer.  If the expression evaluates to a NULL
value or any other value that cannot be losslessly converted to an
integer, an error is returned.  If the LIMIT expression evaluates to a
negative value, then there is no upper bound on the number of rows
returned.  Otherwise, the SELECT returns the first N rows of its result
set only, where N is the value that the LIMIT expression evaluates to. 
Or, if the SELECT statement would return less than N rows without a LIMIT
clause, then the entire result set is returned. 

The expression attached to the optional OFFSET clause that may follow a
LIMIT clause must also evaluate to an integer, or a value that can be
losslessly converted to an integer.  If an expression has an OFFSET
clause, then the first M rows are omitted from the result set returned by
the SELECT statement and the next N rows are returned, where M and N are
the values that the OFFSET and LIMIT clauses evaluate to, respectively. 
Or, if the SELECT would return less than M+N rows if it did not have a
LIMIT clause, then the first M rows are skipped and the remaining rows (if
any) are returned.  If the OFFSET clause evaluates to a negative value,
the results are the same as if it had evaluated to zero. 

Instead of a separate OFFSET clause, the LIMIT clause may specify two
scalar expressions separated by a comma.  In this case, the first
expression is used as the OFFSET expression and the second as the LIMIT
expression.  This is counter-intuitive, as when using the OFFSET clause
the second of the two expressions is the OFFSET and the first the LIMIT. 
This is intentional - it maximizes compatibility with other SQL database
systems. 


Fredrick Ughimi

Hello Fred, yeah forgot about these. Thanks alot.  It  seems Offset means the number of records to be skipped.
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet