Okay, I know a lot of variations on this question have been asked, but here I go.
好的,我知道有很多关于这个问题的变化已被问到,但是我走了。
I'm starting with this query.
我从这个查询开始。
SELECT lprArchived, lprReportId, lprOwner
FROM ReportIndex
WHERE lprArchived = 1
In most cases, each row returned will have a unique value in the lprReportId column. However, for cases where multiple rows have the same value in lprReportId, I only want one row.
在大多数情况下,返回的每一行在lprReportId列中都有一个唯一值。但是,对于lprReportId中多行具有相同值的情况,我只需要一行。
So which one? I would prefer the row where lprOwner = 'ABCD'.
那么哪一个?我更喜欢lprOwner ='ABCD'的行。
Is it possible to write a query that would return unique rows and, in cases where rows were not unique, give me the one that has lprOwner = 'ABCD'?
是否可以编写一个返回唯一行的查询,如果行不唯一,给我一个有lprOwner ='ABCD'的查询?
Note: I believe that only one row will match lprOwner = 'ABCD' for a given lprReportId, but if for some reason there was more than one, I'd still only want one row returned.
注意:我相信对于给定的lprReportId,只有一行匹配lprOwner ='ABCD',但如果由于某种原因有多个,我仍然只想要返回一行。
2 个解决方案
#1
1
Try this: It will take one record per lprReportId and in cases where there are multiple entries with the same lprReportId, it will prioritise ones that have an lprOwner = 'ABCD'
试试这个:每个lprReportId需要一条记录,如果有多个条目有相同的lprReportId,它会优先考虑那些有lprOwner ='ABCD'的条目
SELECT t.Archived, t.ReportID, t.[Owner]
FROM (
SELECT
ROW_NUMBER() OVER ( PARTITION BY lprReportId ORDER BY lprReportId, CASE WHEN lprOwner = 'ABCD' THEN 1 ELSE 10 END ) AS RowNum,
lprArchived AS Archived,
lprReportId AS ReportID,
lprOwner AS [Owner]
FROM
ReportIndex
WHERE
lprArchived = 1
) t
WHERE t.RowNum = 1
#2
0
select top 1
lprArchived,
lprReportId,
lprOwner
from ReportIndex
where
lprArchived = 1
order by case when lprOwner = 'ABCD' then 0 else 1 end asc
This will take all matches, order them with 'ABCD' at the top, and then take the first row. If you have any other criteria for selecting rows, you could add it to the end of the order by clause (most recent, for example).
这将采用所有匹配,在顶部用'ABCD'命令它们,然后取第一行。如果您有任何其他选择行的条件,则可以将其添加到order by子句的末尾(例如,最新的)。
#1
1
Try this: It will take one record per lprReportId and in cases where there are multiple entries with the same lprReportId, it will prioritise ones that have an lprOwner = 'ABCD'
试试这个:每个lprReportId需要一条记录,如果有多个条目有相同的lprReportId,它会优先考虑那些有lprOwner ='ABCD'的条目
SELECT t.Archived, t.ReportID, t.[Owner]
FROM (
SELECT
ROW_NUMBER() OVER ( PARTITION BY lprReportId ORDER BY lprReportId, CASE WHEN lprOwner = 'ABCD' THEN 1 ELSE 10 END ) AS RowNum,
lprArchived AS Archived,
lprReportId AS ReportID,
lprOwner AS [Owner]
FROM
ReportIndex
WHERE
lprArchived = 1
) t
WHERE t.RowNum = 1
#2
0
select top 1
lprArchived,
lprReportId,
lprOwner
from ReportIndex
where
lprArchived = 1
order by case when lprOwner = 'ABCD' then 0 else 1 end asc
This will take all matches, order them with 'ABCD' at the top, and then take the first row. If you have any other criteria for selecting rows, you could add it to the end of the order by clause (most recent, for example).
这将采用所有匹配,在顶部用'ABCD'命令它们,然后取第一行。如果您有任何其他选择行的条件,则可以将其添加到order by子句的末尾(例如,最新的)。