question

There are many ways to get the desired results using:

1. row_number windowing function
2. last_value or first_value
3. max with over clause
4. correlated subquery

For example, numbering the rows within each tenant partition (all rows for the same tenant) so that the latest record gets the number = 1 in the sub-select (or CTE) will allow filtering out all rows where the number is greater than 1. This method will work in any version of SQL Server starting from 2005

-- using row_number() windowing function
;with records as (
    select
        *, row_number() over (partition by TenantID order by Updated desc) N 
        from YourTable
)
    select
        TenantID, Users, Updated
        from records
        where N = 1;

Similarly, the last_value (with order by Updated desc) or first_value (with order by Updated) may be used. This method will work with SQL Server 2012 or any newer version

Also, including the max of Updated for all rows within each Tenant partition will allow a similar sub-select. This method will work with SQL Server 2012 or any newer version.

-- using max with over clause
;with records as (
    select
        *, max(Updated) over (partition by TenantID) LastUpdated 
        from YourTable
)
    select
        TenantID, Users, Updated
        from records
        where LastUpdated = updated;

Finally, the correlated subquery may be used. It is probably going to be not very efficient, but will work with any version of SQL Server, even with very old, long unsupported versions going back to the last millennium.

-- correlated subquery
select
    t.*
    from YourTable t
    where Updated = (
        select 
            max(Updated) 
            from YourTable
            where TenantID = t.TenantID
    );

Out of all the mentioned methods, only the first one (with row number) can guarantee that only one row will be returned per tenant, even if there are records for the same tenant which have the same "latest updated" value. In case if such scenario is not possible then any method will work as expected.

Hope this helps.

Oleg

@,

My apologies for not responding sooner - I actually typed up response as soon as I received it but got distracted and did not hit "send".

While the first query did return the desired result I ended up doing it differently. The original goal was to find the maximum number of users in the last billing cycle. But since I only log tenants that change counts, there is no guarantee that during the given time frame every tenant count will be reported and for that I had to find the latest updated record for each tenant.

So the code ended up looking like this:

/*

	Tenant extension report
	
	10/23/2019 - SGR - added phase 1
	10/25/2019 - SGR - added phase 2

*/
/* 

	Phase 1: 
			Pick up maximum number of extensions for all tenants that had changes during requested
			period

*/
SELECT
	DLS_PBX_Tenant.tenantid as [Tenant Id],
	DLS_PBX_Tenant.name as [Tenant Name],
	max(users) as [Extensions],
	(SELECT price FROM pbxextension WHERE d_active = 'y' AND PBXextension.d_custid = #platid# ) AS [Unit cost],
	convert(varchar,max(users) * (SELECT price FROM pbxextension WHERE d_active = 'y' AND PBXextension.d_custid = #platid# )) as [Cost]
FROM 
	DLS_PBX_tenant 
WHERE 
	updated BETWEEN '#From#' and '#To#' AND
	DLS_pbx_tenant.custid = #platid#
GROUP BY
	DLS_PBX_Tenant.tenantid,
	DLS_PBX_Tenant.name
UNION ALL
/* 

	Phase 2: 
			Pick up latest updated tenant log records for all tenants that had did not have changes during requested
			period

*/

SELECT 
	dls_pbx_tenant.tenantid,
	dls_pbx_tenant.name,
	IsNull(dls_pbx_tenant.users,0) AS [Licenses],
	(SELECT price FROM pbxextension WHERE d_active = 'y' AND PBXextension.d_custid = #platid# ) AS [Unit cost],
	convert(varchar,users) * (SELECT price FROM pbxextension WHERE d_active = 'y' AND PBXextension.d_custid = #platid# ) as [Cost]
FROM 
        dls_pbx_tenant,
		(SELECT t.tenantid, max(t.updated) as updated
		FROM 
		(
		SELECT tenantid, users, updated
		FROM DLS_PBX_tenant
		WHERE updated < '#From#' and custid = #platid# 
		) t
		GROUP BY tenantid) z
WHERE
	dls_pbx_tenant.tenantid = z.tenantid AND
	dls_pbx_tenant.updated = z.updated AND
	dls_pbx_tenant.tenantid NOT IN 
	(
	SELECT
		DISTINCT DLS_PBX_Tenant.tenantid 
	FROM 
		DLS_PBX_tenant 
	WHERE 
		updated BETWEEN '#From#' and '#To#' AND
		DLS_pbx_tenant.custid = #platid#
	)
ORDER BY 
	dls_pbx_tenant.tenantid