ROW_NUMBER() column is invalid and trying to speed up query help.

Question

I am trying to use the ROW_NUMBER function, but I am running into two problems. 1) Because I am ordering the count in descending order, the rowid does not start at 1, it starts at 41 for example. 2) When I try to bring back rowid between 42 and 52, it says rowid is an invalid column. Here is the toned down query with some extra columns taken out so it is more readable:

SELECT ROW_NUMBER() OVER (ORDER BY column1) AS rowid,
hrl.column0
(SELECT top 1 bus from hc WHERE column2 = hc.column2) AS c,
'Site' as Based,
column1, column2, column3, column4,
CASE WHEN status = '1' Then 'Active'
     WHEN status = '2' Then 'Canceled'
ELSE
   'Unknown'
END as column5,
column6,
(select count(*) from hrl where column1 = hrl.column1) as [cnt]
FROM hrl
INNER JOIN hc on hc.column2 = hrl.column2
INNER JOIN rsc on rsc.column3 = hrl.column3
LEFT OUTER JOIN  gmt on gmt.[column7] = hrl.[column7]
where bus LIKE '%test%'
ORDER BY [cnt] desc,rowid
and  rowid >= 1 AND rowid <= 10 -- Says rowid is invalid

Here is the data I want

RowID Business Count

1     Green    50
2     Red      30
3     Blue     20

Here is what I am getting:

RowID Business Count

3     Blue     20
2     Red      30
1     Green    50

Answer 1

Put it into the subquery:

SELECT *
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY column1) AS rowid,
    hrl.column0,
    (SELECT top 1 bus from hc WHERE column2 = hc.column2) AS c,
    'Site' as Based,
    column1, column2, column3, column4,
    CASE WHEN status = '1' Then 'Active'
         WHEN status = '2' Then 'Canceled'
    ELSE
       'Unknown'
    END as column5,
    column6,
    (select count(*) from hrl where column1 = hrl.column1) as [cnt]
    FROM hrl
    INNER JOIN hc on hc.column2 = hrl.column2
    INNER JOIN rsc on rsc.column3 = hrl.column3
    LEFT OUTER JOIN  gmt on gmt.[column7] = hrl.[column7]
    where bus LIKE '%test%'
) sq
WHERE rowid >= 1 AND rowid <= 10
ORDER BY [cnt] DESC

If you just want TOP 10 column1 rows ordered then by [cnt], you may use:

SELECT *
FROM (
    SELECT TOP 10
           hrl.column0,
           (SELECT top 1 bus from hc WHERE column2 = hc.column2) AS c,
           'Site' as Based,
           column1, column2, column3, column4,
           CASE WHEN status = '1' Then 'Active'
                WHEN status = '2' Then 'Canceled'
                ELSE 'Unknown'
           END as column5,
           column6,
           (select count(*) from hrl where column1 = hrl.column1) as [cnt]
    FROM   hrl
    INNER JOIN hc on hc.column2 = hrl.column2
    INNER JOIN rsc on rsc.column3 = hrl.column3
    LEFT OUTER JOIN  gmt on gmt.[column7] = hrl.[column7]
    WHERE  bus LIKE '%test%'
    ORDER BY column1
) sq
ORDER BY [cnt] DESC

Or, if you just need ORDER BY [cnt] DESC, try this:

SELECT TOP 10
       hrl.column0,
       (SELECT top 1 bus from hc WHERE column2 = hc.column2) AS c,
       'Site' as Based,
       column1, column2, column3, column4,
       CASE WHEN status = '1' Then 'Active'
            WHEN status = '2' Then 'Canceled'
            ELSE 'Unknown'
       END as column5,
       column6,
       (select count(*) from hrl where column1 = hrl.column1) as [cnt]
FROM   hrl
INNER JOIN hc on hc.column2 = hrl.column2
INNER JOIN rsc on rsc.column3 = hrl.column3
LEFT OUTER JOIN  gmt on gmt.[column7] = hrl.[column7]
WHERE  bus LIKE '%test%'
ORDER BY [cnt] DESC

Answer 2

Wrap unsorted data as a CTE (Common Table Expression)

;with NotSorted as (
 SELECT ROW_NUMBER() OVER (ORDER BY column1) AS rowid, 
  hrl.column0, 
  (SELECT top 1 bus from hc WHERE column2 = hc.column2) AS c,
  'Site' as Based, column1, column2, column3, column4,
  CASE WHEN status = '1' Then 'Active'
       WHEN status = '2' Then 'Canceled'
       ELSE 'Unknown'
  END as column5, column6,
  (select count(*) from hrl where column1 = hrl.column1) as [cnt]
 FROM hrl
   INNER JOIN hc on hc.column2 = hrl.column2
   INNER JOIN rsc on rsc.column3 = hrl.column3
   LEFT OUTER JOIN  gmt on gmt.[column7] = hrl.[column7]
 where bus LIKE '%test%'
)
select *
from NotSorted
ORDER BY [cnt] desc, rowid and  rowid >= 1 AND rowid <= 10

As for,

and rowid >= 1 AND rowid <= 10 -- Says rowid is invalid

You can't access computed alias directly and you would usually put computed column into a subquery or as a CTE