SQL with CTE that has ROW_NUMBER Over Partition remove rn column

Question

I have a script that returns results that are including the RN = 1 column, So all rows have this value 1 with column name RN.

If I try to change select * to select my as 'column_name' I get strange results where the column names are in all the returned fields.

So how do exclude the rn=1 column?

 --Wrong SQL Edition (Dairy XL)

with cte as
(

SELECT  

--damstaticfarmdata.sfdid,
--TskOsAndSqlCheck.toasId,
-- TskFarmBasic.FrmCountry as 'Region',
-- damstaticfarmdata.sfdlelycentercode as 'Lely Center Code',
LelyCenter.LceName as 'Lely Center Name',
damstaticfarmdata.sfdcustomermovexcode as 'Customer Movex Code',
damstaticfarmdata.sfdfarmname as 'Farm Name',
--damstaticfarmdata.sfdfarmlicensetypeid as 'License Type',
-- CASE 
-- WHEN sfdfarmlicensetypeid = 3 then 'Farm'
-- WHEN sfdfarmlicensetypeid = 4 then 'Farm No Maintenance'
-- END as 'License Type',
-- damstaticfarmdata.sfdcurrentversion as 'Current Version',
--TskFarmBasic.FrmCurrentVersion,
TskOsAndSqlCheck.toasOsInfo as 'Windows Version',
TskOsAndSqlCheck.toasSqlVersion as 'SQL Version',
TskOsAndSqlCheck.toasSqlDatabaseSizeInMB 'Database Size (MB)',
damstaticfarmdata.sfdnrrobots as 'Nr of Robots',
-- damstaticfarmdata.sfdfarmlicense as 'Horizon/T4C Product Key',
--TskFarmBasic.FrmFarmLicense,
TskOsAndSqlCheck.toasTime as 'BM Last Upload Time',
ROW_NUMBER() OVER (PARTITION BY sfdcustomermovexcode ORDER BY toasTime DESC) AS rn
   
FROM            LelyCenter INNER JOIN
                         damstaticfarmdata ON LelyCenter.LceMovexCode = damstaticfarmdata.sfdlelycentercode INNER JOIN
                         TskFarmBasic ON damstaticfarmdata.sfdcustomermovexcode = TskFarmBasic.FrmCustomerMovexCode INNER JOIN
                         TskOsAndSqlCheck ON TskFarmBasic.FrmId = TskOsAndSqlCheck.toasFrmId
Where FrmCountry in ('CA', 'US') and sfdnrrobots > 7 and toasSqlVersion like '%Express%' and toasTime > '2023-01-01'

)
select 
*
from cte where rn = 1 
Order by  'Lely Center Name' asc, 'Farm Name' asc

I want this column gone!

If I try this

select 'Lely Center Name','Customer Movex Code','Farm Name','Windows Version','SQL Version','Database Size (MB)','Nr of Robots','BM Last Upload Time'

I get this

Msg 408, Level 16, State 1, Line 41 A constant expression was encountered in the ORDER BY list, position 1.

The CTE needs to return rn because the outer query references it in its whee clause.. *So how do exclude the rn=1 column?* You do so by replacing `select *` in the outer query with the list of all columns - excluding rn. — GMB, May 01 '23 at 21:42
single quoted strings, e.g. 'Lely Center Name' are typically string constants in SQL. that is why the error messages says "constant expression was encountered" — Kurt, May 01 '23 at 21:47
try putting your aliases in double-quotes like: damstaticfarmdata.sfdfarmname as "Farm Name" and ORDER BY "Farm Name" asc — Kurt, May 01 '23 at 21:49

score 0 · Accepted Answer · answered May 02 '23 at 06:10

You cannot exclude the RN column as long as you use select * in the final step. In short, you must specify all the columns you do want in the output. For this may I suggest that you do NOT add final column aliases into the CTE, instead just retain the source columns names so that you don't need to repeat those "More Complex Column Names". (& I have removed all unused columns) e.g.

--Wrong SQL Edition (Dairy XL)
WITH cte
AS (
    SELECT
          LelyCenter.LceName
        , damstaticfarmdata.sfdcustomermovexcode
        , damstaticfarmdata.sfdfarmname
        , TskOsAndSqlCheck.toasOsInfo
        , TskOsAndSqlCheck.toasSqlVersion
        , TskOsAndSqlCheck.toasSqlDatabaseSizeInMB
        , damstaticfarmdata.sfdnrrobots
        , TskOsAndSqlCheck.toasTime
        , ROW_NUMBER() OVER (
            PARTITION BY sfdcustomermovexcode ORDER BY toasTime DESC
            ) AS rn
    FROM LelyCenter
    INNER JOIN damstaticfarmdata ON LelyCenter.LceMovexCode = damstaticfarmdata.sfdlelycentercode
    INNER JOIN TskFarmBasic ON damstaticfarmdata.sfdcustomermovexcode = TskFarmBasic.FrmCustomerMovexCode
    INNER JOIN TskOsAndSqlCheck ON TskFarmBasic.FrmId = TskOsAndSqlCheck.toasFrmId
    WHERE FrmCountry IN ('CA', 'US')
        AND sfdnrrobots > 7
        AND toasSqlVersion LIKE '%Express%'
        AND toasTime > '2023-01-01'
    )
SELECT
          LceName                   AS "Lely Center Name"
        , sfdcustomermovexcode      AS "Customer Movex Code"
        , sfdfarmname               AS "Farm Name"
        , toasOsInfo                AS "Windows Version"
        , toasSqlVersion            AS "SQL Version"
        , toasSqlDatabaseSizeInMB   AS "Database Size (MB)"
        , sfdnrrobots               AS "Nr of Robots"
        , toasTime                  AS "BM Last Upload Time"
FROM cte
WHERE rn = 1
ORDER BY
      "Lely Center Name" ASC
    , "Farm Name" ASC

btw: it is more conventional to use double quotes for those column aliases

SQL with CTE that has ROW_NUMBER Over Partition remove rn column

1 Answers1