选择第一个和最后一个元素的最有效方法，SQLite？

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

从SQLite中的列中选择第一个和最后一个元素的最有效方法是什么?

6 个解决方案

#1

The first and last element from a row?

一行中的第一个和最后一个元素?

SELECT column1, columnN
FROM mytable;

I think you must mean the first and last element from a column:

我认为你必须指的是列中的第一个和最后一个元素:

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

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

有关MIN()和MAX()的信息,请参见http://www.sqlite.org/lang_aggfunc.html。

I'm using First and Last as column aliases.

我使用First和Last作为列别名。

#2

if it's just one column:

如果它只是一列:

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

if you want to select whole row:

如果要选择整行:

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

#3

The most efficient way would be to know what those fields were called and simply select them.

最有效的方法是知道这些字段被调用的内容并简单地选择它们。

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

#4

Probably like this:

可能是这样的:

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

You get minor efficiency enhancements from specifying the table name and schema.

通过指定表名和模式,您可以获得较小的效率增强。

#5

min()/max() approach is wrong. It is only correct, if the values are ascending only. I needed something liket this for currency rates, which are random raising and falling.

min()/ max()方法是错误的。如果值仅是升序,则它是正确的。我需要一些类似于货币汇率的东西,这是随机的上涨和下跌。

This is my solution:

这是我的解决方案:

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: the subselect results in a single row with the columns first,last containing rowid's.

魔法第1部分:子选择导致单行首先包含列,最后包含rowid。

magic part 2 easy to filter on those two rowid's.

魔法部分2容易过滤那两个rowid的。

This is the best solution I've come up so far. Hope you like it.

这是我到目前为止提出的最佳解决方案。希望你喜欢。

#6

We can do that by the help of Sql Aggregate function, like Max and Min. These are the two aggregate function which help you to get last and first element from data table .

我们可以借助Sql Aggregate函数来实现,比如Max和Min。这两个聚合函数可以帮助您从数据表中获取最后一个元素。

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

Max will give you the max value means last value and min will give you the min value means it will give you the First value, from the specific table.

Max会给你最大值意味着最后一个值而min会给你最小值意味着它会从特定表中给你第一个值。

#1