Sunil Sunil - 5 months ago 7
SQL Question

For a result set of N rows, populate a certain column value only for the first row in these N rows when using SQL Server 2014

I have a SQL query in SQL Server 2014 as shown below. My problem is that the column of

TotalRecordsCount
is being returned for each and every row in the returned result set.

I would like to populate the column
TotalRecordsCount
only for first row in the result set. So, if the result set has 10 rows and TotalRecordsCount is 5000, then only the first row will have a value of 5000 for this column and all the other rows will have a null value for this column.

Question : How would I do this using the query below?

WITH x AS
(
SELECT u.UserId,
u.UserName,
u.Email,
u.Firstname + isnull(' ' + u.Lastname, '') userfullname,
u.Activated,
[dbo].[xyz_fn_getUserRoles](u.userid) VendorUserRoles
FROM dbo.Users U
INNER JOIN dbo.SupplierUsers su
ON u.UserId = su.UserId
WHERE su.SupplierId = @SupplierId
AND u.UserName IS NOT NULL ),
TotalRecords AS (SELECT Count(UserId) TotalRecordsCount FROM x)
SELECT x.UserId,
x.UserName,
x.Email,
x.UserFullName,
x.Activated,
x.UserRoles,
TotalRecordsCount
FROM x OFFSET @startRowIndex rows FETCH next @maximumRows rows only;

vkp vkp
Answer

Wrap the final select after the cte's with one more select to show Totalrecords only on the first row in the resultset based on a specified order. (I assume the order by is for userid's, if not you should change it.)

Also, you were not joining the TotalRecords cte. The query in question would result in an error.The query below fixes it with a cross join.

WITH x AS 
(SELECT     u.UserId, 
                      u.UserName, 
                      u.Email, 
                      u.Firstname + isnull(' ' + u.Lastname, '') userfullname, 
                      u.Activated, 
                      [dbo].[xyz_fn_getUserRoles](u.userid) VendorUserRoles 
           FROM       dbo.Users U 
           INNER JOIN dbo.SupplierUsers su 
           ON         u.UserId = su.UserId 
           WHERE      su.SupplierId = @SupplierId 
           AND        u.UserName IS NOT NULL ), 
          TotalRecords AS (SELECT Count(UserId) TotalRecordsCount FROM x) 
SELECT t1.*, 
case when row_number() over(order by t1.UserID) = 1 then t2.TotalRecordsCount end 
as TotalRecordsCount
FROM (
SELECT x.UserId, 
       x.UserName, 
       x.Email, 
       x.UserFullName, 
       x.Activated, 
       x.UserRoles
FROM   x OFFSET @startRowIndex rows FETCH next @maximumRows rows only
) t1 
CROSS JOIN TotalRecords t2;