SQL 在 Postgres 中获取表的最后一条记录

Get last record of a table in Postgres

提问by Luc

I'm using Postgres and cannot manage to get the last record of my table:

我正在使用 Postgres 并且无法获得我表的最后一条记录：

 my_query = client.query("SELECT timestamp,value,card from my_table");

How can I do that knowning that timestamp is a unique identifier of the record ?

知道时间戳是记录的唯一标识符，我该怎么做？

回答by bpgergo

If under "last record"you mean the record which has the latest timestamp value, then try this:

如果在“最后一条记录”下您指的是具有最新时间戳值的记录，请尝试以下操作：

my_query = client.query("
  SELECT TIMESTAMP,
    value,
    card
  FROM my_table
  ORDER BY TIMESTAMP DESC
  LIMIT 1
");

回答by chl

you can use

您可以使用

SELECT timestamp, value, card 
FROM my_table 
ORDER BY timestamp DESC 
LIMIT 1

assuming you want also to sort by timestamp?

假设你也想排序timestamp？

回答by phsaires

If you accept a tip, create an id in this table like serial. The default of this field will be:

如果您接受提示，请在此表中创建一个 id，如序列号。该字段的默认值为：

nextval('table_name_field_seq'::regclass).

So, you use a query to call the last register. Using your example:

因此，您使用查询来调用最后一个寄存器。使用您的示例：

pg_query($connection, "SELECT currval('table_name_field_seq') AS id;

I hope this tip helps you.

我希望这个提示可以帮助你。

回答by Fabian Pijcke

Easy way: ORDER BY in conjunction with LIMIT

简单的方法：ORDER BY 与 LIMIT 结合使用

SELECT timestamp, value, card
FROM my_table
ORDER BY timestamp DESC
LIMIT 1;

However, LIMIT is not standard and as stated by Wikipedia, The SQL standard's core functionality does not explicitly define a default sort order for Nulls.. Finally, only one row is returned when several records share the maximum timestamp.

但是， LIMIT 不是标准的，正如Wikipedia 所述，SQL 标准的核心功能没有明确定义 Null 的默认排序顺序。. 最后，当多条记录共享最大时间戳时，只返回一行。

Relational way:

关系方式：

The typical way of doing this is to check that no row has a higher timestamp than any row we retrieve.

这样做的典型方法是检查没有任何行的时间戳比我们检索的任何行都高。

SELECT timestamp, value, card
FROM my_table t1
WHERE NOT EXISTS (
  SELECT *
  FROM my_table t2
  WHERE t2.timestamp > t1.timestamp
);

It is my favorite solution, and the one I tend to use. The drawback is that our intent is not immediately clear when having a glimpse on this query.

这是我最喜欢的解决方案，也是我倾向于使用的解决方案。缺点是当我们瞥见这个查询时，我们的意图并不是很清楚。

Instructive way: MAX

指导方式：MAX

To circumvent this, one can use MAX in the subquery instead of the correlation.

为了规避这一点，可以在子查询中使用 MAX 而不是相关性。

SELECT timestamp, value, card
FROM my_table
WHERE timestamp = (
  SELECT MAX(timestamp)
  FROM my_table
);

But without an index, two passes on the data will be necessary whereas the previous query can find the solution with only one scan. That said, we should not take performances into consideration when designing queries unless necessary, as we can expect optimizers to improve over time. However this particular kind of query is quite used.

但是如果没有索引，则需要对数据进行两次传递，而前一个查询只需扫描一次即可找到解决方案。也就是说，除非必要，否则我们在设计查询时不应考虑性能，因为我们可以期望优化器会随着时间的推移而改进。然而，这种特殊类型的查询非常常用。

Show off way: Windowing functions

炫耀方式：加窗函数

I don't recommend doing this, but maybe you can make a good impression on your boss or something ;-)

我不建议这样做，但也许你可以给你的老板或其他人留下好印象；-)

SELECT DISTINCT
  first_value(timestamp) OVER w,
  first_value(value) OVER w,
  first_value(card) OVER w
FROM my_table
WINDOW w AS (ORDER BY timestamp DESC);

Actually this has the virtue of showing that a simple query can be expressed in a wide variety of ways (there are several others I can think of), and that picking one or the other form should be done according to several criteria such as:

实际上，这表明一个简单的查询可以用多种方式表达（我可以想到其他几种方式），并且应该根据几个标准来选择一种或另一种形式，例如：

• portability (Relational/Instructive ways)
• efficiency (Relational way)
• expressiveness (Easy/Instructive way)

• 可移植性（关系/指导性方式）
• 效率（关系方式）
• 表现力（简单/指导性方式）

回答by Allan Registos

The last inserted record can be queried using this assuming you have the "id" as the primary key:

假设您将“id”作为主键，则可以使用此查询最后插入的记录：

SELECT timestamp,value,card FROM my_table WHERE id=(select max(id) from my_table)

Assuming every new row inserted will use the highest integer value for the table's id.

假设插入的每个新行都将使用表 ID 的最高整数值。

回答by LEMUEL ADANE

Use the following

使用以下

SELECT timestamp, value, card 
FROM my_table 
ORDER BY timestamp DESC 
LIMIT 1

回答by Vignesh Raja

To get the last row,

要获得最后一行，

Get Last row in the sorted order: In case the table has a column specifying time/primary key,

按排序顺序获取最后一行：如果表有一列指定时间/主键，

1. Using LIMITclause

1. 使用LIMIT条款

SELECT * FROM USERS ORDER BY CREATED_TIME DESC LIMIT 1;

SELECT * FROM USERS ORDER BY CREATED_TIME DESC LIMIT 1;

1. Using FETCHclause - Reference

2. 使用FETCH条款 -参考

SELECT * FROM USERS ORDER BY CREATED_TIME FETCH FIRST ROW ONLY;

SELECT * FROM USERS ORDER BY CREATED_TIME FETCH FIRST ROW ONLY;

Get Last row in the rows insertion order: In case the table has no columns specifying time/any unique identifiers

获取行插入顺序中的最后一行：如果表没有指定时间/任何唯一标识符的列

1. Using CTIDsystem column, where ctidrepresents the physical location of the row in a table - Reference

   SELECT * FROM USERS WHERE CTID = (SELECT MAX(CTID) FROM USERS);

3. 使用CTID系统列，其中ctid表示行在表中的物理位置 -参考

   SELECT * FROM USERS WHERE CTID = (SELECT MAX(CTID) FROM USERS);

Consider the following table,

考虑下表，

userid |username |    createdtime |
     1 |       A |  1535012279455 |
     2 |       B |  1535042279423 | //as per created time, this is the last row
     3 |       C |  1535012279443 |
     4 |       D |  1535012212311 |
     5 |       E |  1535012254634 | //as per insertion order, this is the last row

The query 1 and 2 returns,

查询 1 和 2 返回，

userid |username |    createdtime |
     2 |       B |  1535042279423 |

while 3 returns,

当 3 次返回时，

userid |username |    createdtime |
     5 |       E |  1535012254634 |

Note: On updating an old row, it removes the old row and updates the data and inserts as a new row in the table. So using the following query returns the tuple on which the data modification is done at the latest.

注意：在更新旧行时，它会删除旧行并更新数据并作为新行插入表中。因此，使用以下查询将返回最晚对其进行数据修改的元组。

Now updating a row, using

现在更新一行，使用

UPDATE USERS SET USERNAME = 'Z' WHERE USERID='3'

UPDATE USERS SET USERNAME = 'Z' WHERE USERID='3'

the table becomes as,

桌子变成了，

userid |username |    createdtime |
     1 |       A |  1535012279455 |
     2 |       B |  1535042279423 |
     4 |       D |  1535012212311 |
     5 |       E |  1535012254634 |
     3 |       Z |  1535012279443 |

Now the query 3 returns,

现在查询 3 返回，

userid |username |    createdtime |
     3 |       Z |  1535012279443 |

回答by Hoser

If your table has no Id such as integer auto-increment, and no timestamp, you can still get the last row of a table with the following query.

如果您的表没有 Id（例如整数自动增量），也没有时间戳，您仍然可以使用以下查询获取表的最后一行。

select * from <tablename> offset ((select count(*) from <tablename>)-1)

For example, that could allow you to search through an updated flat file, find/confirm where the previous version ended, and copy the remaining lines to your table.

例如，这可以让您搜索更新的平面文件，查找/确认先前版本的结束位置，并将剩余的行复制到您的表中。

回答by G Gordon Worley III

These are all good answers but if you want an aggregate function to do this to grab the last row in the result set generated by an arbitrary query, there's a standard way to do this(taken from the Postgres wiki, but should work in anything conforming reasonably to the SQL standard as of a decade or more ago):

这些都是很好的答案，但是如果您希望聚合函数执行此操作以获取由任意查询生成的结果集中的最后一行，则有一种标准方法可以执行此操作（取自 Postgres wiki，但应该适用于任何符合与十年或更早以前的 SQL 标准相当）：

-- Create a function that always returns the last non-NULL item
CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE SQL IMMUTABLE STRICT AS $$
        SELECT ;
$$;

-- And then wrap an aggregate around it
CREATE AGGREGATE public.LAST (
        sfunc    = public.last_agg,
        basetype = anyelement,
        stype    = anyelement
);

It's usually preferable to do select ... limit 1if you have a reasonable ordering, but this is useful if you need to do this within an aggregate and would prefer to avoid a subquery.

select ... limit 1如果您有合理的排序，通常最好这样做，但如果您需要在聚合中执行此操作并且希望避免子查询，这将很有用。

See also this questionfor a case where this is the natural answer.

另请参阅此问题以了解这是自然答案的情况。

回答by Sandy

The column name plays an important role in the descending order:

列名在降序中起着重要作用：

select <COLUMN_NAME1, COLUMN_NAME2> from >TABLENAME> ORDER BY <COLUMN_NAME THAT MENTIONS TIME> DESC LIMIT 1;

For example: The below-mentioned table(user_details) consists of the column name 'created_at' that has timestamp for the table.

例如：下面提到的表(user_details) 包含具有表时间戳的列名“created_at”。

SELECT userid, username FROM user_details ORDER BY created_at DESC LIMIT 1;