如何从SQL获得独特的ros [英] How to get unique ros from SQL

问题描述

我有这样的价格。

 TaskId InternalId（无列名）UploadId RequestId CreatedDate ProcessStatusMappingID 
 959 959 0 183 959636241330158000000 3月3日197 
 959 959 0 183 959636241463837000000 3/3/2017 197 
 960 960 0 183 960636241330205000000 3/3/2017 197 
 960 960 0 183 960636241463912000000 3 / 3/2017 197 
 961 961 0 183 961636241330219000000 3/3/2017 197 
 983 983 0 194 983636244258262000000 3/6/2017 197 
 983 983 0 194 983636244258532000000 3/6/2017 197 
 984 984 0 194 984636244258309000000 3/6/2017 197 
 984 984 0 194 984636244258553000000 3/6/2017 197 
 985 985 0 194 985636244258327000000 3/6/2017 197 

i需要查询才能返回值

 TaskId InternalId（No column name）UploadId RequestId CreatedDate ProcessStatusMappingID 
 959 959 0 183 959636241463837000000 3/3/2017 197 
 960 960 0 183 960636241 463912000000 3/3/2017 197 
 961 961 0 183 961636241330219000000 3/3/2017 197 
 983 983 0 194 983636244258532000000 3/6/2017 197 
 984 984 0 194 984636244258553000000 3/6 / 2017 197 
 985 985 0 194 985636244258327000000 3/6/2017 197 

感谢advnace。



我尝试了什么：



尝试使用不同但不起作用。

  SELECT  * 
  FROM   TABLE  T 
  INNER   JOIN  
（
  SELECT  TASKID， MAX（REQUESTID）RID 
  FROM   TABLE  T 
）TMP  ON  T.TASKID = TMP.TASKID  AND  T.REQUESTID = TMP.RID 

WITH DEDUPE AS（

SELECT *

，ROW_NUMBER（）OVER（按TaskId划分的TaskId ORDER）发生

FROM table_name

）

SELECT * FROM DEDUPE

WHERE

OCCURENCE = 1

没有重复项。所有行都是独特的！所有您想要达到的目标是获得最高 RequestId 的日期。



我建议使用 MAX（）聚合函数和OVER（）子句 [< a href =https://technet.microsoft.com/en-us/library/ms187751(v=sql.110).aspx\"target =_ blanktitle =New Window> ^ ]：

  SELECT   DISTINCT  TaskId，InternalId， NoColumnName，UploadId，
 MAX（RequestId） OVER （ PARTITION   BY  TaskId  ORDER   BY  CreatedDate） AS  MaxRequestId，CreatedDate，ProcessStatusMappingID 
  FROM  YourTableName 

I have a vales like this.

TaskId	InternalId	(No column name)	UploadId	RequestId	CreatedDate	ProcessStatusMappingID
959	959	0	183	959636241330158000000	3/3/2017	197
959	959	0	183	959636241463837000000	3/3/2017	197
960	960	0	183	960636241330205000000	3/3/2017	197
960	960	0	183	960636241463912000000	3/3/2017	197
961	961	0	183	961636241330219000000	3/3/2017	197
983	983	0	194	983636244258262000000	3/6/2017	197
983	983	0	194	983636244258532000000	3/6/2017	197
984	984	0	194	984636244258309000000	3/6/2017	197
984	984	0	194	984636244258553000000	3/6/2017	197
985	985	0	194	985636244258327000000	3/6/2017	197

i require a query to return the value has

TaskId	InternalId	(No column name)	UploadId	RequestId	CreatedDate	ProcessStatusMappingID
959	959	0	183	959636241463837000000	3/3/2017	197
960	960	0	183	960636241463912000000	3/3/2017	197
961	961	0	183	961636241330219000000	3/3/2017	197
983	983	0	194	983636244258532000000	3/6/2017	197
984	984	0	194	984636244258553000000	3/6/2017	197
985	985	0	194	985636244258327000000	3/6/2017	197

thanks in advnace.

What I have tried:

tried with distinct but not working.

解决方案

Try this,

SELECT *
FROM   TABLE T
       INNER JOIN 
       (
           SELECT TASKID, MAX(REQUESTID) RID
           FROM   TABLE T
       ) TMP ON T.TASKID = TMP.TASKID AND T.REQUESTID = TMP.RID

WITH DEDUPE AS (
SELECT *
, ROW_NUMBER() OVER ( PARTITION BY TaskId ORDER BY TaskId ) AS OCCURENCE
FROM table_name
)
SELECT * FROM DEDUPE
WHERE
OCCURENCE = 1

There's no duplicates. All rows are unique! All what you want to achieve, is to get date with the highest RequestId.

I'd suggest to use MAX() aggregate function with OVER() clause[^]:

SELECT DISTINCT TaskId,	InternalId,	NoColumnName,	UploadId,
	MAX(RequestId) OVER( PARTITION BY TaskId ORDER BY CreatedDate) AS MaxRequestId,	CreatedDate,	ProcessStatusMappingID
FROM YourTableName

这篇关于如何从SQL获得独特的ros的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！