Want to get the Max step, from the following select

Transact-SQL
1

I have a bunch of columns, and want to get all rows with max(wf.STEP) only.

Not getting a way to bring., if i use group by clause , then i need to put all columns into group by. which is not possible.

how can i get all rows that has max(wf.step) thanks a lot for the helpful info..

Select wf.ActivityID,
dbo.f_GetProg_Proj_Ctr(wf.ModuleName,wf.ModuleRecordID) as "Dept - Proceso - Tipo de ID",
rm.ContractorRefNo as "No de ID",
rm.sequenceno as "No de Radicacion",
rm.barcode as Expedient,
dbo.f_GetModuleTitle(wf.ModuleName,wf.ModuleRecordID) as Titulo,
CONVERT(varchar(12), rm.documentdate, 103) as "Fecha Radicado",
CONVERT(varchar(12), rm.ReceivedDate, 103) as "Fecha Recibido",
(Select SpanishText from TAB_ccsNetPickLists where fieldlabelkey = 'lbllogtype' and PickID= rm.LogTypeID) as "Tipo(Canal)",
(Select SpanishText from TAB_ccsNetPickLists where fieldlabelkey = 'lblDocumentSource' and PickID= rm.SubjectID) as "Comite",
wf.step,
(Select username from tab_ccsnetusers where userid = wf.AssignedTo) as Asignado,
wf.Description as descripcion,
CONVERT(varchar(12), wf.duedate, 103) as Vencimento,
CONVERT(varchar(12), wf.donedate, 103) as Realizada,
---CONVERT(date, CONVERT(varchar(10), wf.duedate, 105), 105) AS VenceDate,
(Select SpanishText from TAB_ccsNetPickLists where fieldlabelkey = 'ActivityType' and PickID= wf.Type) as Type,

CONVERT(varchar(20), DATEDIFF(Day, wf.duedate, wf.donedate)) as Days,

	   [days] = CASE WHEN wf.duedate < GetDate() THEN -1 * DATEDIFF(Day, wf.duedate, GetDate())
					 ELSE CONVERT(varchar(20), DATEDIFF(Day, GetDate(), wf.duedate))
				END
FROM Tab_ccsNetWorkflowActivity wf, TAB_ccsNetRM rm

where wf.ModuleName='rm' and wf.modulerecordid=rm.rmid
--and datediff(d,getdate(),duedate) <= 30
---and critical=1
and dbo.f_GetModuleTitle(wf.ModuleName,wf.ModuleRecordID) <> ''
and rm.ProgID = 71
and rm.documentdate BETWEEN cast('01/01/2015' as datetime)
AND GETDATE()

2

How about something like this:

WITH cte (
    SELECT wf.modulerecordid, MAX(wf.step) AS MaxStep 
    FROM Tab_ccsNetWorkflowActivity wf
    INNER JOIN TAB_ccsNetRM rm ON wf.modulerecordid = rm.rmid
    WHERE wf.ModuleName = 'rm'
	    AND dbo.f_GetModuleTitle(wf.ModuleName, wf.ModuleRecordID) <> ''
	    AND rm.ProgID = 71
	    AND rm.documentdate BETWEEN cast('01/01/2015' AS DATETIME) AND GETDATE()
    GROUP BY wf.modulerecordid 
)
SELECT wf.ActivityID
	,dbo.f_GetProg_Proj_Ctr(wf.ModuleName, wf.ModuleRecordID) AS "Dept - Proceso - Tipo de ID"
	,rm.ContractorRefNo AS "No de ID"
	,rm.sequenceno AS "No de Radicacion"
	,rm.barcode AS Expedient
	,dbo.f_GetModuleTitle(wf.ModuleName, wf.ModuleRecordID) AS Titulo
	,CONVERT(VARCHAR(12), rm.documentdate, 103) AS "Fecha Radicado"
	,CONVERT(VARCHAR(12), rm.ReceivedDate, 103) AS "Fecha Recibido"
	,(
		SELECT SpanishText
		FROM TAB_ccsNetPickLists
		WHERE fieldlabelkey = 'lbllogtype'
			AND PickID = rm.LogTypeID
		) AS "Tipo(Canal)"
	,(
		SELECT SpanishText
		FROM TAB_ccsNetPickLists
		WHERE fieldlabelkey = 'lblDocumentSource'
			AND PickID = rm.SubjectID
		) AS "Comite"
	,wf.step
	,(
		SELECT username
		FROM tab_ccsnetusers
		WHERE userid = wf.AssignedTo
		) AS Asignado
	,wf.Description AS descripcion
	,CONVERT(VARCHAR(12), wf.duedate, 103) AS Vencimento
	,CONVERT(VARCHAR(12), wf.donedate, 103) AS Realizada
	,
	---CONVERT(date, CONVERT(varchar(10), wf.duedate, 105), 105) AS VenceDate, 
	(
		SELECT SpanishText
		FROM TAB_ccsNetPickLists
		WHERE fieldlabelkey = 'ActivityType'
			AND PickID = wf.Type
		) AS Type
	,CONVERT(VARCHAR(20), DATEDIFF(Day, wf.duedate, wf.donedate)) AS Days
	,[days] = CASE 
		WHEN wf.duedate < GetDate()
			THEN - 1 * DATEDIFF(Day, wf.duedate, GetDate())
		ELSE CONVERT(VARCHAR(20), DATEDIFF(Day, GetDate(), wf.duedate))
		END
FROM Tab_ccsNetWorkflowActivity wf
INNER JOIN TAB_ccsNetRM rm ON wf.modulerecordid = rm.rmid 
INNER JOIN cte ON wf.modulerecordid = cte.modulerecordid 
WHERE wf.ModuleName = 'rm'
	AND dbo.f_GetModuleTitle(wf.ModuleName, wf.ModuleRecordID) <> ''
	AND rm.ProgID = 71
	AND rm.documentdate BETWEEN cast('01/01/2015' AS DATETIME) AND GETDATE();
3

I used a derived table rather than a CTE: if you prefer, you can convert the derived table below into a CTE:

Select wf.ActivityID,
dbo.f_GetProg_Proj_Ctr(wf.ModuleName,wf.ModuleRecordID) as "Dept - Proceso - Tipo de ID",
rm.ContractorRefNo as "No de ID",	
rm.sequenceno as "No de Radicacion",
rm.barcode as Expedient,
dbo.f_GetModuleTitle(wf.ModuleName,wf.ModuleRecordID) as Titulo,
CONVERT(varchar(12), rm.documentdate, 103) as "Fecha Radicado",
CONVERT(varchar(12), rm.ReceivedDate, 103) as "Fecha Recibido",
(Select SpanishText from TAB_ccsNetPickLists where fieldlabelkey = 'lbllogtype' and PickID= rm.LogTypeID) as "Tipo(Canal)",	
(Select SpanishText from TAB_ccsNetPickLists where fieldlabelkey = 'lblDocumentSource' and PickID= rm.SubjectID) as "Comite",	
wf.step,
(Select username from tab_ccsnetusers where userid = wf.AssignedTo) as Asignado,
wf.Description as descripcion, 
CONVERT(varchar(12), wf.duedate, 103) as Vencimento,
CONVERT(varchar(12), wf.donedate, 103) as Realizada,	
---CONVERT(date, CONVERT(varchar(10), wf.duedate, 105), 105) AS VenceDate,	
(Select SpanishText from TAB_ccsNetPickLists where fieldlabelkey = 'ActivityType' and PickID= wf.Type) as Type,

CONVERT(varchar(20), DATEDIFF(Day, wf.duedate, wf.donedate)) as Days,

	   [days] = CASE WHEN wf.duedate < GetDate() THEN -1 * DATEDIFF(Day, wf.duedate, GetDate())
					 ELSE CONVERT(varchar(20), DATEDIFF(Day, GetDate(), wf.duedate))
				END
FROM (
    SELECT ActivityID, ModuleName, ModuleRecordID, ModuleName, ModuleRecordIDm, 
        step, AssignedTo, Description, duedate, donedate, Type,
        ROW_NUMBER() OVER(PARTITION BY ModuleRecordID ORDER BY step DESC) AS row_num
    FROM Tab_ccsNetWorkflowActivity
    WHERE ModuleName='rm'
      and ProgID = 71
      and documentdate BETWEEN '20150101' AND GETDATE()
      and dbo.f_GetModuleTitle(ModuleName,ModuleRecordID) <> ''
     --and datediff(d,getdate(),duedate) <= 30 
     ---and critical=1

) AS wf, TAB_ccsNetRM rm
where wf.modulerecordid=rm.rmid