I need to select records that contain the maximum value of one field (queryresolutiondate) in a table, then within that subset, I need to select just the records that contain the minimum value of another field (queryestablishdate). I have tried to select the min and max in a single sub-select, but unfortunately, the absolute min and max do not always occur in the same record, so no records are returned.
我需要选择包含表中一个字段(queryresolutiondate)的最大值的记录，然后在该子集中，只需要选择包含另一个字段的最小值的记录(queryfoundshdate)。我尝试在一个子选择中选择最小值和最大值，但是不幸的是，绝对最小值和最大值并不总是出现在同一个记录中，所以不会返回任何记录。
THe following query works, but this code is part of a much larger query that returns approx 45 columns, and I would prefer not to group 44 columns:
下面的查询是有效的，但是这段代码是一个更大的查询的一部分，该查询返回大约45列，我不希望组44列:
select
q.[SourceCustomerId],
q.[SourceProductCode],
q.[SourceProductIssueNum],
min (q.[QueryEstablishDate]),
q.[SourceQueryCode],
q.[SourceQueryStatus],
q.[QueryResolutionDate]
from [dbo]..[dQueryAll] q with (nolock)
inner join (select [SourceCustomerId], [SourceProductCode], [SourceProductIssueNum], [SourceQueryCode],
max([QueryResolutionDate]) as maxQueryResolutionDate
from [dbo]..[dQueryAll] with (nolock)
where [SourceQueryCode] = 311
group by [sourceCustomerId], [SourceProductCode], [SourceProductIssueNum], [SourceQueryCode]) qg
on (qg.[SourceCustomerId] = q.[SourceCustomerId] and qg.[SourceProductCode] = q.[SourceProductCode]
and qg.[SourceProductIssueNum] = q.[SourceProductIssueNum]
and qg.maxQueryResolutionDate = q.[QueryResolutionDate])
group by
q.[SourceCustomerId],
q.[SourceProductCode],
q.[SourceProductIssueNum],
q.[SourceQueryCode],
q.[SourceQueryStatus],
q.[QueryResolutionDate]
I wondered if I could create another sub-select within the subselect above to select the min establish date from the rocrds that contained the max resloution date. If this is possible, I would like some help
我想知道我是否可以在上面的子选择中创建另一个子选择，以从包含max resloution日期的rocrd中选择最小值。如果可能的话，我想要一些帮助
I have an example of the data in the table that I have pasted into Excel, but cannot find how to load it here.
我在表格中有一个粘贴到Excel中的数据示例，但无法找到如何在这里加载它。
1 个解决方案
#1
0
You can nest selects, however if there is a good relevant index, grouping by a large number columns should not be a performance problem.
您可以嵌套select，但是如果有一个很好的相关索引，那么使用大量列进行分组应该不是性能问题。
As to nesting, just write the inner sub query, then write the outer one if it were running ona flat table. the replace the flat subquery with the inner sub query.
至于嵌套，只需编写内部子查询，如果外部查询在一个平面表上运行，则编写它。将平面子查询替换为内部子查询。
HTH
HTH
#1
0
You can nest selects, however if there is a good relevant index, grouping by a large number columns should not be a performance problem.
您可以嵌套select，但是如果有一个很好的相关索引，那么使用大量列进行分组应该不是性能问题。
As to nesting, just write the inner sub query, then write the outer one if it were running ona flat table. the replace the flat subquery with the inner sub query.
至于嵌套，只需编写内部子查询，如果外部查询在一个平面表上运行，则编写它。将平面子查询替换为内部子查询。
HTH
HTH