ROW_NUMBER() VS. DISTINCT
Posted
by ramadan2050
on Stack Overflow
See other posts from Stack Overflow
or by ramadan2050
Published on 2010-04-14T07:41:08Z
Indexed on
2010/04/14
7:43 UTC
Read the original article
Hit count: 382
sql
Dear All, I have a problem with ROW_NUMBER() , if i used it with DISTINCT in the following Query I have 2 scenarios: 1- run this query direct : give me for example 400 record as a result 2- uncomment a line which start with [--Uncomment1--] : give me 700 record as a result it duplicated some records not all the records
what I want is to solve this problem or to find any way to show a row counter beside each row, to make a [where rownumber between 1 and 30] --Uncomment2--
if I put the whole query in a table, and then filter it , it is work but it still so slow
waiting for any feedback and I will appreciate that
Thanks in advance
SELECT * FROM (SELECT Distinct CRSTask.ID AS TaskID, CRSTask.WFLTaskID, --Uncomment1-- ROW_NUMBER() OVER (ORDER By CRSTask.CreateDate asc ) AS RowNum , CRSTask.WFLStatus AS Task_WFLStatus, CRSTask.Name AS StepName, CRSTask.ModifiedDate AS Task_ModifyDate, CRSTask.SendingDate AS Task_SendingDate, CRSTask.ReceiveDate AS Task_ReceiveDate, CRSTask.CreateDate AS Task_CreateDate, CRS_Task_Recipient_Vw.Task_CurrentSenderName, CRS_Task_Recipient_Vw.Task_SenderName, CRS_INFO.ID AS CRS_ID, CRS_INFO.ReferenceNumber, CRS_INFO.CRSBeneficiaries,
CRS_INFO.BarCodeNumber,
ISNULL(dbo.CRS_FNC_GetTaskReceiver(CRSTask.ID), '') + ' ' + ISNULL
(CRS_Organization.ArName, '') AS OrgName, CRS_Info.IncidentID, COALESCE(CRS_Subject.ArSubject, '??? ????') AS ArSubject, COALESCE(CRS_INFO.Subject, 'Blank Subject') AS CRS_Subject, CRS_INFO.Mode, CRS_Task_Recipient_Vw.ReceiverID, CRS_Task_Recipient_Vw.ReceiverType, CRS_Task_Recipient_Vw.CC, Temp_Portal_Users_View.ID AS CRS_LockedByID, Temp_Portal_Users_View.ArabicName AS CRS_LockedByName, CRSDraft.ID AS DraftID, CRSDraft.Type AS DraftType, CASE WHEN CRS_Folder = 1 THEN Task_SenderName WHEN CRS_Folder = 2 THEN Task_SenderName WHEN CRS_Folder = 3 THEN Task_CurrentSenderName END AS SenderName, CRS_Task_Folder_Vw.CRS_Folder, CRS_INFO.Status, CRS_INFO.CRS_Type, CRS_Type.arName AS CRS_Type_Name FROM CRS_Task_Folder_Vw LEFT OUTER JOIN CRSTask ON CRSTask.ID = CRS_Task_Folder_Vw.TaskID LEFT OUTER JOIN CRS_INFO ON CRS_INFO.ID = CRSTask.CRSID LEFT OUTER JOIN CRS_Subject ON COALESCE( SUBSTRING( CRS_INFO.Subject, CHARINDEX('_', CRS_INFO.Subject) + 1, LEN(CRS_INFO.Subject) ), 'Blank Subject' ) = CRS_Subject.ID LEFT OUTER JOIN CRSInfoAttribute ON CRS_INFO.ID = CRSInfoAttribute.ID LEFT OUTER JOIN CRS_Organization ON CRS_Organization.ID = CRSInfoAttribute.SourceID LEFT OUTER JOIN CRS_Type ON CRS_INFO.CRS_Type = CRS_Type.ID LEFT OUTER JOIN CRS_Way ON CRS_INFO.CRS_Send_Way = CRS_Way.ID LEFT OUTER JOIN CRS_Priority ON CRS_INFO.CRS_Priority_ID = CRS_Priority.ID LEFT OUTER JOIN CRS_SecurityLevel ON CRS_INFO.SecurityLevelID = CRS_SecurityLevel.ID LEFT OUTER JOIN Portal_Users_View ON Portal_Users_View.ID = CRS_INFO.CRS_Initiator LEFT OUTER JOIN AD_DOC_TBL ON CRS_INFO.DocumentID = AD_DOC_TBL.ID LEFT OUTER JOIN CRSTask AS Temp_CRSTask ON CRSTask.ParentTask = Temp_CRSTask.ID LEFT OUTER JOIN Portal_Users_View AS Temp_Portal_Users_View ON Temp_Portal_Users_View.ID = AD_DOC_TBL.Lock_User_ID LEFT OUTER JOIN Portal_Users_View AS Temp1_Portal_Users_View ON Temp1_Portal_Users_View.ID = CRS_INFO.ClosedBy LEFT OUTER JOIN CRSDraft ON CRSTask.ID = CRSDraft.TaskID LEFT OUTER JOIN CRS_Task_Recipient_Vw ON CRSTask.ID = CRS_Task_Recipient_Vw.TaskID --LEFT OUTER JOIN CRSTaskReceiverUsers ON CRSTask.ID =
CRSTaskReceiverUsers.CRSTaskID AND CRS_Task_Recipient_Vw.ReceiverID = CRSTaskReceiverUsers.ReceiverID
LEFT OUTER JOIN CRSTaskReceiverUserProfile
ON CRSTask.ID = CRSTaskReceiverUserProfile.TaskID
WHERE Crs_Info.SUBJECT <> 'Blank Subject'
AND (CRS_INFO.Subject NOT LIKE '%null%')
AND CRS_Info.IsDeleted <> 1
/* AND CRSTask.WFLStatus <> 6
AND CRSTask.WFLStatus <> 8 */
AND (
(
CRS_Task_Recipient_Vw.ReceiverID IN (1, 29)
AND CRS_Task_Recipient_Vw.ReceiverType IN (1, 3, 4)
)
)
AND 1 = 1
)Codes --Uncomment2-- WHERE Codes.RowNum BETWEEN 1 AND 30 ORDER BY Codes.Task_CreateDate ASC
© Stack Overflow or respective owner