什么是在SQLite中检索包含特定列中特定值的表中最近一行的最佳方法?

huangapple go评论71阅读模式
英文:

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 bylimit

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

huangapple
  • 本文由 发表于 2020年10月20日 04:38:38
  • 转载请务必保留本文链接:https://go.coder-hub.com/64434881.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定