选择与 SQLite 中的条件匹配的最新行

发布于 2024-09-24 20:28:51 字数 404 浏览 3 评论 0原文

假设我有一个表：

Name, status, timestamp

我想选择与 status='active' 匹配的行，但仅选择那些具有最新时间戳的行。因此，如果有这样的行：

Bob, active, 5/10/2010
Bob, active, 6/12/2010
Ann, inactive, 6/12/2000
Ann, active, 9/3/2009
Ann, active, 9/25/2010

我希望它返回：

Bob, active, 6/12/2010
Ann, active, 9/25/2010

我该怎么做？我正在使用 SQLite，如果有的话。

谢谢。

原文

Let's say I have a table:

Name, status, timestamp

And I want to select the rows that match status='active' but only those that have the most recent timestamp for each of them. So if there were rows like this:

Bob, active, 5/10/2010
Bob, active, 6/12/2010
Ann, inactive, 6/12/2000
Ann, active, 9/3/2009
Ann, active, 9/25/2010

I'd want it to return:

Bob, active, 6/12/2010
Ann, active, 9/25/2010

How can I do this? I'm using SQLite, if it matters.

Thank you.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

各空 2024-10-01 20:28:51

select name, status, max(timestamp) 
from my_table 
where status = 'active' 
group by name, status

看看http://www.w3schools.com/sql/sql_groupby.asp

select name, status, max(timestamp) 
from my_table 
where status = 'active' 
group by name, status

take a look at http://www.w3schools.com/sql/sql_groupby.asp

回复收藏 0 原文

弥繁 2024-10-01 20:28:51

以下是我解决此类问题的方法。您希望每个名称占一行，这样不存在具有相同名称和更大时间戳的其他行：

SELECT t1.*
FROM MyTable t1
LEFT OUTER JOIN MyTable t2 ON t1.name = t2.name 
  AND t1.timestamp < t2.timestamp
WHERE t2.name IS NULL

但这仍然可以为每个名称返回多行，因为给定名称可能有多个具有相同最大时间戳的行。因此，使用主键作为决胜局：

SELECT t1.*
FROM MyTable t1
LEFT OUTER JOIN MyTable t2 ON t1.name = t2.name 
  AND (t1.timestamp < t2.timestamp OR t1.timestamp = t2.timestamp AND t1.id < t2.id)
WHERE t2.name IS NULL

我假设 id 是此示例的主键，但任何其他按时间顺序增加值的唯一列都可以。

Here's how I solve this type of problem. You want one row for each name such that no other row exists with the same name and a greater timestamp:

SELECT t1.*
FROM MyTable t1
LEFT OUTER JOIN MyTable t2 ON t1.name = t2.name 
  AND t1.timestamp < t2.timestamp
WHERE t2.name IS NULL

But this can still return multiple rows for each name, because you could have more than one row for a given name with the same max timestamp. So use the primary key as a tie-breaker:

SELECT t1.*
FROM MyTable t1
LEFT OUTER JOIN MyTable t2 ON t1.name = t2.name 
  AND (t1.timestamp < t2.timestamp OR t1.timestamp = t2.timestamp AND t1.id < t2.id)
WHERE t2.name IS NULL

I'm assuming id is a primary key for this example, but any other unique column that increases in value chronologically would work.

回复收藏 0 原文

夜未央樱花落 2024-10-01 20:28:51

比尔，我相信我现在已经可以使用它了（似乎在不同的测试数据上选择了正确的行）。我使用了您的查询并添加了条件 t1.active_status!='active'，因为这将处理任何不活动的情况。整个查询看起来像：

SELECT t1.*<br>
FROM TrialTypes  t1<br>
LEFT OUTER JOIN TrialTypes t2 ON t1.name = t2.name <br>
  AND (t1.start_date < t2.start_date OR t1.start_date = t2.start_date AND t1.rowid < t2.rowid)<br>
WHERE t2.name IS NULL and t1.active_status != 'active'<br>

非常感谢您的帮助。显然，我对基本的 SQL 查询不熟悉。这有助于教我，谢谢！

Bill, I believe I have it working now (seems to pick the right rows on different test data). I used your query and added the condition t1.active_status!='active', since that will take care of anything inactive. The whole query looks like:

SELECT t1.*<br>
FROM TrialTypes  t1<br>
LEFT OUTER JOIN TrialTypes t2 ON t1.name = t2.name <br>
  AND (t1.start_date < t2.start_date OR t1.start_date = t2.start_date AND t1.rowid < t2.rowid)<br>
WHERE t2.name IS NULL and t1.active_status != 'active'<br>

Thank you very much for the help. Obviously, I'm new to more than basic SQL queries. This helps teach me, thanks!

回复收藏 0 原文

~没有更多了~