What is wrong with this query? Can't get ROW_NUMBER() to work.

Question

SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY hrl.Frn) as Row,
  hrl.unq, hrl.LcnsId, hc.Business,hc.Name,hc.Phone,
  hrl.Frn,hrl.CallSign, hrl.gsamarkettypeid, 
  gmt.[Market Type Code] + ' - ' + gmt.gsamarkettype,
  hrl.gsalatitude,hrl.gsalongitude,
  rsc.RadioServiceCode + ' - ' + rsc.RadioService, 
                    GrantDt, ExpirationDt, EffectiveDt, 
  CancellationDt
  FROM dbo.sbi_f_HldrRgstrtnLcns hrl
  INNER JOIN dbo.sbi_f_HldrCntcts hc on 
                    hc.CallSign = hrl.CallSign
  INNER JOIN dbo.sbi_l_radioservicecodes rsc on 
                    rsc.radioservicecodeid = hrl.radioservicecodeid
  LEFT OUTER JOIN dbo.sbi_l_GSAMarketTypes gmt on 
                    gmt.GSAMarketTypeId = hrl.GSAMarketTypeId
  WHERE hc.Entity_Type = 'L'  AND hrl.LicenseStatusId IN (1)
  and Row >=1 and Row <= 20) -- The error occurs here,
                    -- it says incorrect syntax near )

Answer 1

You can't use an alias for a column in a where clause, because the where clause is processed before the Select Clause.

EDITED:

You could also just add the subquery in the where clause

Where ((SELECT ROW_NUMBER() OVER (ORDER BY hrl.Frn)) < 20,

or without usingt Row_Number at all,

Where (Select Count(*) 
From dbo.sbi_f_HldrRgstrtnLcns 
Where Frn < hrl.Frn) < 20

Answer 2

Move your Row criteria into the outer select

SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY hrl.Frn) as Row,
     ...
               WHERE hc.Entity_Type = 'L'  AND hrl.LicenseStatusId IN (1)
              ) T
WHERE T.Row >=1 and T.Row <= 20)

Answer 3

You could do this using a CTE:

WITH NumberedRows AS
(
    SELECT 
        ROW_NUMBER() OVER (ORDER BY hrn.Frl) AS RowNum,
        ...
    WHERE 
        hc.Entity_Type = 'L'
        AND hrl.LicenseStatusId IN (1)
)
SELECT
    *
FROM
    NumberedRows
WHERE
    RowNum <= 20

Answer 4

check which version of sql server database are you using. The row_number is added from sql server 2005 onwards. It will not support sql server 2000