使用SQL查询查找最后一天的代码

I want to run a query to find out the last day a code was used by each Company.

我想运行一个查询，找出每个公司使用代码的最后一天。

SELECT T1.comp, T1,code, T1 date
FROM T1

I want to add MAX to T1.date but unsure of the sytax ... or something similar

我想把MAX加到T1。日期，但不确定sytax…或类似的东西

Perhaps I can create a DATE field by concatenating T1.period & T1,yr but struggle with syntax ..

也许我可以通过连接T1创建一个日期字段。周期和T1,yr，但与语法作斗争。

DateValue(Str(T1.period) & Str(T1.yr))

Much appreciated!

感谢!

4 个解决方案

#1

Order the table on the date descending, and keep only the first row:

按日期降序排列，只保留第一行:

SELECT TOP 1 T1.comp, T1.code, T1.date
FROM T1
ORDER BY T1.date DESC

#2

You need an aggregate function. you can find more details here: http://www.sqlcourse2.com/agg_functions.html

你需要一个聚合函数。您可以在这里找到更多细节:http://www.sqlcourse2.com/agg_functions.html

for the mean time try:

同时尝试:

SELECT T1.comp, max(T1 date)
FROM T1
GROUP BY t1.comp

#3

By the comments on @ThomasG answer I assume you want the max for each comp that has more then 300 records?

通过对@ThomasG答案的评论，我假设您想要的是每条记录超过300条的comp的最大值?

If so, use GROUP BY and HAVING :

如果有，用GROUP BY和have:

SELECT T1.comp, max(T1 date)
FROM T1
GROUP BY t1.comp
HAVING COUNT(*) >= 300

#4

try this,

试试这个,

SELECT T1.comp, T1.code, MAX(T1.date) AS LastDate
FROM T1
GROUP BY T1.comp, T1.code

#1