sql查询获取最早的日期

if i have a table with columns id, name, score, date

如果我有一个列id，名称，分数，日期的表

and i wanted to run a sql query to get the record where id = 2 with the earliest date in the data set.

我想运行一个SQL查询来获取记录，其中id = 2，数据集中的最早日期。

can you do this within the query or do you need to loop after the fact ?

你可以在查询中执行此操作，还是需要在事后循环？

EDIT: To be explicit, I want to get all of the fields of that record . .

编辑：要明确，我想获得该记录的所有字段。。

5 个解决方案

#1

If you just want the date:

如果您只想要日期：

SELECT MIN(date) as EarliestDate
FROM YourTable
WHERE id = 2

If you want all of the information:

如果您想要所有信息：

SELECT TOP 1 id, name, score, date
FROM YourTable
WHERE id = 2
ORDER BY Date

Prevent loops when you can. Loops often lead to cursors, and cursors are almost never necessary and very often really inefficient.

尽可能防止循环。循环通常会导致游标，游标几乎从不需要，而且通常效率很低。

#2

SELECT TOP 1 ID, Name, Score, [Date]
FROM myTable
WHERE ID = 2
Order BY [Date]

#3

Try

尝试

select * from dataset
where id = 2
order by date limit 1

Been a while since I did sql, so this might need some tweaking.

自从我做了sql以来已经有一段时间了，所以这可能需要一些调整。

#4

Using "limit" and "top" will not work with all SQL servers (for example with Oracle). You can try a more complex query in pure sql:

使用“limit”和“top”不适用于所有SQL服务器（例如使用Oracle）。您可以在纯sql中尝试更复杂的查询：

select mt1.id, mt1."name", mt1.score, mt1."date" from mytable mt1
where mt1.id=2
and mt1."date"= (select min(mt2."date") from mytable mt2 where mt2.id=2)

#5

While using TOP or a sub-query both work, I would break the problem into steps:

虽然使用TOP或子查询都可以工作，但我会将问题分解为几个步骤：

Find target record

查找目标记录

SELECT MIN( date ) AS date, id
FROM myTable
WHERE id = 2
GROUP BY id

Join to get other fields

加入以获得其他领域

SELECT mt.id, mt.name, mt.score, mt.date
FROM myTable mt
INNER JOIN
( 
   SELECT MIN( date ) AS date, id
   FROM myTable
   WHERE id = 2
   GROUP BY id
) x ON x.date = mt.date AND x.id = mt.id

While this solution, using derived tables, is longer, it is:

虽然使用派生表的此解决方案更长，但它是：

Easier to test
更容易测试
Self documenting
自我记录
Extendable
可扩展

It is easier to test as parts of the query can be run standalone.

它更容易测试，因为查询的某些部分可以独立运行。

It is self documenting as the query directly reflects the requirement ie the derived table lists the row where id = 2 with the earliest date.

它是自我记录的，因为查询直接反映了需求，即派生表列出了id = 2且具有最早日期的行。

It is extendable as if another condition is required, this can be easily added to the derived table.

它可以扩展，就好像需要另一个条件一样，这可以很容易地添加到派生表中。

#1