用于查找最小日期的SQL语句

I am new to SQL so I am fumbling here a bit. I have the following table:

我是SQL的新手，所以我在这里摸索了一下。我有下表：

Entered             Generalist               Item
12/31/2012 07:26:50 Tom Smith                RTW/Updates
12/31/2012 07:30:10 Terrie Bradshaw          Posters
12/31/2012 07:38:16 Jen Lopez                Client Assistance/Request
12/31/2012 07:48:00 Tom Smith                RTW/Updates
12/31/2012 07:50:29 Mike Smith               RTW/Updates
12/31/2012 07:55:32 Tom Smith                Client Assistance/Request

I am trying to find out when was the last time a rep was assigned an item. So I am looking for the Min value on a column. My query would look at Item "RTW/Updates" when was the earlier time entered between a date range and return Tom Smith. For example the user queries, RTW/Update between 12/31/2012 and 1/1/2013 and the answer would be Tom Smith.

我想知道最后一次为一个代表分配一个项目的时间。所以我在列上寻找最小值。我的查询将查看项目“RTW /更新”，这是在日期范围和返回Tom Smith之间输入的较早时间。例如，用户在12/31/2012和2013年1月1日之间查询RTW / Update，答案是Tom Smith。

This is what I have so far, but have not been able to figure out the between the dates part:

这是我到目前为止，但未能弄清楚日期之间的部分：

    SELECT MIN(entered), generalist, item
FROM dataTable

That is pretty much it.

这就是它。

6 个解决方案

#1

I believe this should work (where the @ variables are the parameters passed to your procedure)

我相信这应该工作（@变量是传递给你的过程的参数）

SELECT MIN(entered), generalist, item
FROM dataTable
WHERE item = @itemParm
AND entered BETWEEN @enteredStart AND @enteredEnd
GROUP BY generalist, item

#2

I May not understand what you want, but if you want to get one person back based on the minimum date, you need to work out the minimum date, and use that to find that person:

我可能不明白你想要什么，但是如果你想根据最短的日期让一个人回来，你需要计算出最短的约会日期，并用它来找到那个人：

select
*
from
datatable
where
entered = 
(
select
min(entered) as MinDate
from
DataTable
where
Item = 'RTW/Updates'
  )
and item = 'RTW/Updates'

SQL Fiddle

SQL小提琴

You could also use a CTE:

您也可以使用CTE：

; with LowDate as
(select
min(entered) as MinDate
from
DataTable
where
Item = 'RTW/Updates' )

select
*
from
datatable
inner join LowDate
ON entered = LowDate.MinDate
and item = 'RTW/Updates'

More SQL Fiddle!

更多SQL小提琴！

#3

You are looking for the window functions. Here is an example:

您正在寻找窗口功能。这是一个例子：

select generalist, item, entered
from (SELECT generalist, item, entered,
             row_number() over (partition by item order by entered desc) as seqnum
      FROM dataTable
     ) t
where seqnum = 1;

The function row_number() enumerates the rows for each item (based on the partition by clause) starting with 1. The row with 1 is going to have the most recent date, because of the order by clause.

函数row_number（）枚举每个项目的行（基于partition by子句），从1开始。由于order by子句，带有1的行将具有最新的日期。

The outer query just selects the rows where the seqnum = 1 -- which is the most recent record for each item.

外部查询只选择seqnum = 1的行 - 这是每个项目的最新记录。

#4

Use GROUP BY

使用GROUP BY

SELECT MAX(entered) MinDate, generalist, item FROM dataTable
GROUP BY Generalist, Item

#5

Just to add something to the answers (which are correct, essentially) before this one : in order to find the latest date, you'll have to use MAX() instead of MIN().

只是在这个答案之前添加一些东西（基本上是正确的）：为了找到最新的日期，你必须使用MAX（）而不是MIN（）。

#6

SELECT MIN(entered) MinDate, generalist, item
FROM dataTable
GROUP BY generalist, item

When you have used an aggregate function in your select statement and you are also selecting other column which are not contained in any aggregate function you have to tell sql server how to aggregate that column by adding a GROUP BY clause and mentioning names of all the column that are in SELECT statement but not contained in any Aggregate function.

如果在select语句中使用了聚合函数，并且还在选择任何聚合函数中未包含的其他列，则必须通过添加GROUP BY子句并提及所有列的名称来告诉sql server如何聚合该列。在SELECT语句中但不包含在任何聚合函数中。

to get the latest Date you will need to get the Biggest(Largest in Number Date) and you will need to use MAX() function instead of MIN(), MIN() will return the Oldest (smallest Date) in your column.

要获得最新日期，您将需要获得最大（数字日期最大），您将需要使用MAX（）函数而不是MIN（），MIN（）将返回列中最旧（最小日期）。

SELECT MAX(entered) MinDate, generalist, item
FROM dataTable
GROUP BY generalist, item

#1