英文:
What is the best way to retrieve the most recent row in a table that contains a specific value in a specific column in SQLite?
问题
请注意,以下是您提供的内容的翻译部分:
假设我有一个包含五行的表格。除了一列中的信息以外,每一行的信息对我来说大多都不相关,这一列是COL_CHAR:
|---------------------|------------------|------------------|
| COL_ID | COL_NAME | COL_CHAR |
|---------------------|------------------|------------------|
| 01 | 名称 01 | X |
|---------------------|------------------|------------------|
| 02 | 名称 02 | Y |
|---------------------|------------------|------------------|
| 03 | 名称 03 | Z |
|---------------------|------------------|------------------|
| 04 | 名称 04 | X |
|---------------------|------------------|------------------|
| 05 | 名称 05 | Y |
|---------------------|------------------|------------------|
我想要从包含最底部/最近的X的行中检索信息 - 在这种情况下,是第四行。在我正在处理的上下文中,X代表应用程序执行的特定操作的最新发生,找到最近的X所属的行对于检索其他信息很重要。
进一步简化,我想要找到最近添加的值为“X”的行,然后从相同的行中检索COL_NAME的值。
我编写的所有相关代码都是用Kotlin编写的,但如果更容易的话,我也能理解Java代码。
英文:
Say I have a table with five rows. The information in each row is mostly irrelevant to me aside from the information in one column, COL_CHAR:
|---------------------|------------------|------------------|
| COL_ID | COL_NAME | COL_CHAR |
|---------------------|------------------|------------------|
| 01 | Name 01 | X |
|---------------------|------------------|------------------|
| 02 | Name 02 | Y |
|---------------------|------------------|------------------|
| 03 | Name 03 | Z |
|---------------------|------------------|------------------|
| 04 | Name 04 | X |
|---------------------|------------------|------------------|
| 05 | Name 05 | Y |
|---------------------|------------------|------------------|
I would like to retrieve information from the row where COL_CHAR contains the bottom-most/most recent X - in this case, the fourth row. In the context of what I'm working on, X represents the most recent occurrence of a specific action performed by the application, and it's important for the row the most recent X belongs to to be used to retrieve other information.
To simplify further, I want to find the most recently-added row where the value of COL_CHAR is "X", then retrieve the value of COL_NAME from the same row.
All of the relevant code I've written is in Kotlin, but I can understand Java code fine, if that's easier.
答案1
得分: 0
这看起来像是 order by
和 limit
:
select t.*
from mytable t
where col_char = 'X'
order by col_id desc limit 1
英文:
This looks like order by
and limit
:
select t.*
from mytable t
where col_char = 'X'
order by col_id desc limit 1
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论