选择具有另一列最大值的不同行 [英] Select distinct rows with max value of another column

问题描述

我正在查询文档管理数据库，其中文档可以具有多个版本.因此，由于版本控制，我在下面的查询多次返回同一文档.

I am querying a document management database where documents can have many versions. So my query below returns the same document multiple times because of the versioning.

 SELECT distinct d.DOCNUM, d.DOCNAME,P.FolderName
    FROM DOCUMENT as d with (nolock)
    inner join Project_T as pt on d.docnum=pt.item_id
    inner join Projects p on pt.prj_id=p.prj_id
   where d.type = 'Personal' and d.owner like '%67360'

结果:

DOCNUM     | DOCNAME     | FOLDERNAME |
-----------+----------+---------------+
123         Article        Jonathan
123         Article part1  Jonathan
256         Meeting Notes  Jonathan
5697        Memo           Jonathan

理想的docnum 123应该只返回一次.

Ideally docnum 123 should only return once.

我已经加入了保存每个文档版本的表格，并且正在选择最新版本(MAX(h.version)，因此我的查询应该每行返回唯一的文档编号.

I have joined the table that holds the versions for each document and am selecting the latest version (MAX(h.version) so my query should return unique document numbers per row.

    SELECT distinct d.DOCNUM, h.version, d.DOCNAME,P.FolderName
    FROM DOCUMENT as d with (nolock)
    inner join DOCHISTORY as h on h.DOCNUM = d.docnum
    inner join Project_T as pt on d.docnum=pt.item_id
    inner join Projects p on pt.prj_id=p.prj_id
   where d.type = 'Personal' and d.owner like '%67360'
   and d.SECURITY = 'P'
   AND h.VERSION = (SELECT MAX(x.version) FROM DOCHISTORY as X 
                    where x.docnum = d.DOCNUM) 

但是这次我获得了最新版本，但是docname略有不同，它多次返回相同的docnum.参见下面的docnum 123.

but this time I get the latest versions however, where docname is slightly different, it returns the same docnum multiple times. see docnum 123 below.

DOCNUM     | Version      | DOCNAME     | FOLDERNAME |
-----------+--------------+-------------+------------+
123          9              Article        Jonathan
123          9              Article part1  Jonathan
256          1              Meeting Notes  Jonathan
5697         21             Memo           Jonathan

我需要在报告中显示docname列.请问有办法吗?

I need to display the docname column in my report. Is there a way aorund this please?

推荐答案

要使用窗口函数，您要尝试做的事情要容易得多.这是一种方法:

What you are trying to do is much easier with window functions. Here is one way:

select DOCNUM, version, DOCNAME, FolderName
from (SELECT d.DOCNUM, h.version, d.DOCNAME, P.FolderName,
             max(h.version) over (partition by d.docnum) as maxversion
      FROM DOCUMENT  d with (nolock)
           inner join DOCHISTORY as h on h.DOCNUM = d.docnum
           inner join Project_T as pt on d.docnum=pt.item_id
           inner join Projects p on pt.prj_id=p.prj_id
     where d.type = 'Personal' and d.owner like '%67360' and d.SECURITY = 'P'
    ) d
where version = maxversion;

表达式max(h.version) over (partition by d.docnum)是一个称为窗口函数(或某些数据库中的解析函数)的结构.这样做是为每个d.docnum值返回最大h.version(基于partition by子句).这是ANSI标准SQL，大多数数据库都支持此功能.

The expression max(h.version) over (partition by d.docnum) is a construct called a window function (or analytic function in some databases). What is does is return the maximum of h.version for each value of d.docnum (based on the partition by clause). This is ANSI standard SQL and most databases support this functionality.

您的版本可能不起作用，因为计算最大值的子查询没有使用与外部查询相同的过滤条件.

Your version is probably not working because the subquery that calculates the maximum value is not using the same filtering criteria as the outer query.

这篇关于选择具有另一列最大值的不同行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！