SQL get single value inside existing query?

Question

Hi :)

I have a query that returns a bunch of rows. But using the same query i would like to:

1. get the total row count in the table
2. get the row number where a certian username is located

Right now im doing like so:

BEGIN
 DECLARE @startRowIndex INT;
 DECLARE  @PageIndex INT;
 DECLARE  @RowsPerPage INT;

 SET @PageIndex = 0;
 SET @RowsPerPage = 15;
 SET @startRowIndex = (@PageIndex * @RowsPerPage) + 1;

 WITH messageentries
      AS (SELECT   Row_number()
                  OVER(ORDER BY score DESC) AS row,
                Count(DISTINCT town.townid) AS towns,
                user_details.username,
                user_score.score,
                allience.alliencename,
                allience.allienceid,
                allience.alliencetagname,
                (SELECT Count(* ) FROM   user_details) AS numberofrows
       FROM     user_details
                INNER JOIN user_score
                  ON user_details.username = user_score.username
                INNER JOIN town
                  ON user_details.username = town.townownername
                LEFT OUTER JOIN allience_roles
                  ON user_details.useralliencerole = allience_roles.roleid
                LEFT OUTER JOIN allience
                  ON allience_roles.allienceid = allience.allienceid
       GROUP BY user_details.username,
                user_score.score,
                allience.alliencename,
                allience.allienceid,
                allience.alliencetagname)
 SELECT *, (SELECT row FROM messageentries WHERE username = 'myUsername') AS myself
 FROM   messageentries
 WHERE  row BETWEEN @startRowIndex AND @StartRowIndex + @RowsPerPage - 1
END

That works, but isn't the two nested selects going to run once for every row in the table? :/

    ...
  (SELECT Count(* ) FROM   user_details) AS numberofrows
    ...
  (SELECT row FROM messageentries WHERE username = 'myUsername') AS myself

So my question being how can i get the values i want as "low-cost" as possible, and preferably in the same query?

Thanks in advance :)

Answer 1

try this...

DECLARE @NumberOfRows INT

SELECT @NumberOfRows = Count(* ) FROM   user_details
 WITH messageentries
      AS (SELECT   Row_number()
                  OVER(ORDER BY score DESC) AS row,
                Count(DISTINCT town.townid) AS towns,
                user_details.username,
                user_score.score,
                allience.alliencename,
                allience.allienceid,
                allience.alliencetagname,
                @NumberOfRows AS numberofrows
       FROM     user_details
                INNER JOIN user_score
                  ON user_details.username = user_score.username
                INNER JOIN town
                  ON user_details.username = town.townownername
                LEFT OUTER JOIN allience_roles
                  ON user_details.useralliencerole = allience_roles.roleid
                LEFT OUTER JOIN allience
                  ON allience_roles.allienceid = allience.allienceid
       GROUP BY user_details.username,
                user_score.score,
                allience.alliencename,
                allience.allienceid,
                allience.alliencetagname)
 SELECT *, MyRowNumber.row AS myself
 FROM   messageentries,
     (SELECT row FROM messageentries WHERE username = 'myUsername') MyRowNumber
 WHERE  row BETWEEN @startRowIndex AND @StartRowIndex + @RowsPerPage - 1

Answer 2

(SELECT Count(* ) FROM   user_details)

This one will be cached (most probably materialized in a Worktable).

(SELECT row FROM messageentries WHERE username = 'myUsername')

For this one, most probably a Lazy Spool (or Eager Spool) will be built, which will be used to pull this value.