The most efficient way to select the 1st and last SQLite element?

Question

What is the most efficient way to select only the first and last items from a column in SQLite?

Answer 1

The first and last item from a string?

SELECT column1, columnN
FROM mytable;

I think you should understand the first and last element from the column:

SELECT MIN(column1) AS First,
       MAX(column1) AS Last
FROM mytable;

---

See http://www.sqlite.org/lang_aggfunc.html for MIN()and MAX().

I use Firstand Lastlike the column aliases.

Answer 2

if it is only one column:

SELECT min(column) as first, max(column) as last FROM table

if you want to select the whole line:

SELECT 'first',* FROM table ORDER BY column DESC LIMIT 1
UNION 
SELECT 'last',* FROM table ORDER BY column ASC LIMIT 1

Answer 3

- , , .

SELECT `first_field`, `last_field` FROM `table`;

Answer 4

, :

SELECT dbo.Table.FirstCol, dbo.Table.LastCol FROM Table

.

Answer 5

min()/max() . , . - , .

:

select st.*
from stats_ticker st,
    (
        select min(rowid) as first, max(rowid) as last --here is magic part 1
        from stats_ticker
        -- next line is just a filter I need in my case. 
        -- if you want first/last of the whole table leave it out.
        where timeutc between datetime('now', '-1 days') and datetime('now')
        ) firstlast
WHERE 
    st.rowid = firstlast.first     --and these two rows do magic part 2
    OR st.rowid = firstlast.last
ORDER BY st.rowid;

magic part 1:, , rowid.

2 rowid.

, . , .

Answer 6

Sql Aggregate, Max Min. , .

Select max (column_name ), min(column name) from table name

Max , Min min, .

Answer 7

: MIN() MAX() AAAA TTTT, . , .

( .stats ) , 94 :

select * from
     (select col1 from mitable limit 1)
union 
select * from 
     (select col1 from mitable limit 1 offset
                             (select count(0) from mitable) -1);

(281 624 718).

Then it is much simpler (works if the table was created WITHOUT ROWID ) [sql keywords in capital letters]:

SELECT col1 FROM mitable
WHERE ROWID = (SELECT MIN(ROWID) FROM mitable) 
   OR ROWID = (SELECT MAX(ROWID) FROM mitable);

This ran 55 steps of the virtual machine in one table and gave the same answer.