CODEWITHSUNDEEP
sqlsql-server
user979331
user979331

Reputation: 11961

SQL ROW_NUMBER() always return 1 for each row

I have this part of a query here:

(
    SELECT ROW_NUMBER() OVER (ORDER BY V_CONSTAT_ACTUAL_DATES.DATE_TO_END) AS 'SortOrder'
    FOR XML PATH(''), Type
)

This is for XML. My issue is that ROW_NUMBER() always returns 1, how come it's not returning a different number for each row?

FULL Query:

Select
(

Select cast ('<'+ V_CONSTAT_ACTUAL_DATES.JOB_NUMBER + '>' +
  cast(

   (Select


        (
        SELECT CONVERT(date, V_CONSTAT_ACTUAL_DATES.DATE_TO_END) AS 'closingDate'
        FOR XML PATH(''), Type
        ),
        (
        SELECT DATEDIFF(dd,V_CONSTAT_BASE_DATES.ID67,V_CONSTAT_ACTUAL_DATES.DATE_TO_END)-1 AS 'DaysOfConstruction'
        FOR XML PATH(''), Type
        ),
        (
        SELECT DATEDIFF(dd,GETDATE(),V_CONSTAT_ACTUAL_DATES.DATE_TO_END) AS 'DaysToClosing'
        FOR XML PATH(''), Type
        ),
        (
            SELECT
            CASE WHEN COALESCE(V_CONSTAT_ACTUAL_DATES.IDNOTES2, '') = '' THEN ' ' ELSE V_CONSTAT_ACTUAL_DATES.IDNOTES2 END AS 'notes'
            FOR XML PATH(''), Type
        ),
        (
        SELECT DATEDIFF(dd,V_CONSTAT_BASE_DATES.ID187,V_CONSTAT_PROJ_DATES.ID187) AS 'ScheduleVariance'
        FOR XML PATH(''), Type
        ),
        (
        SELECT SortOrder FROM 
    (SELECT ROW_NUMBER() OVER
        (ORDER BY V_CONSTAT_ACTUAL_DATES.DATE_TO_END)
            AS 'SortOrder') AS SubQuery
FOR XML PATH(''), Type
        )


    for xml path(''))
    as varchar(max)

    )
    + '</'+ V_CONSTAT_ACTUAL_DATES.JOB_NUMBER + '>'
 as xml)
 )
 from ((homefront.dbo.V_CONSTAT_PROJ_DATES V_CONSTAT_PROJ_DATES INNER JOIN homefront.dbo.V_CONSTAT_ACTUAL_DATES V_CONSTAT_ACTUAL_DATES 
        ON 
            V_CONSTAT_PROJ_DATES.JOB_NUMBER=V_CONSTAT_ACTUAL_DATES.JOB_NUMBER) 
        INNER JOIN 
            homefront.dbo.V_CONSTAT_BASE_DATES V_CONSTAT_BASE_DATES 
        ON 
            (V_CONSTAT_ACTUAL_DATES.JOB_NUMBER=V_CONSTAT_BASE_DATES.JOB_NUMBER) AND (V_CONSTAT_PROJ_DATES.JOB_NUMBER=V_CONSTAT_BASE_DATES.JOB_NUMBER)) 
        INNER JOIN 
            homefront.dbo.V_CONSTAT_SCH_DATES V_CONSTAT_SCH_DATES 
        ON 
            ((V_CONSTAT_BASE_DATES.JOB_NUMBER=V_CONSTAT_SCH_DATES.JOB_NUMBER) AND (V_CONSTAT_PROJ_DATES.JOB_NUMBER=V_CONSTAT_SCH_DATES.JOB_NUMBER)) 
            AND (V_CONSTAT_ACTUAL_DATES.JOB_NUMBER=V_CONSTAT_SCH_DATES.JOB_NUMBER)



WHERE V_CONSTAT_ACTUAL_DATES.AREA_DESC = 'Ancaster Augusta Ph 4(A) Condos' AND V_CONSTAT_ACTUAL_DATES.DATE_TO_END>=GETDATE()
ORDER BY V_CONSTAT_ACTUAL_DATES.DATE_TO_END
FOR XML PATH(''), ROOT('Root')

Upvotes: 0

Views: 3165

Answers (3)

Paul Maxwell
Paul Maxwell

Reputation: 35623

It is common for analytic functions to be used through a derived table so that the column is produced and then accessed later by a subsequent clauses via the column alias. It is particularly common when needing to use row_number() results in a where clause. e.g.

select * from (select *
                  , row_number(partition by X order by Y) as rn
               from table1
               ) as d
where d.rn = 1

Here I believe the same logic applies, you want to calculate a sortorder column THEN place the data into an XML result. My guess is you want to partition by job number.

FROM (
  SELECT
        *
      , ROW_NUMBER() OVER (PARTITION BY V_CONSTAT_ACTUAL_DATES.JOB_NUMBER
                            ORDER BY V_CONSTAT_ACTUAL_DATES.DATE_TO_END) AS 'SortOrder'
  FROM homefront.dbo.V_CONSTAT_PROJ_DATES V_CONSTAT_PROJ_DATES
        INNER JOIN homefront.dbo.V_CONSTAT_ACTUAL_DATES V_CONSTAT_ACTUAL_DATES ON V_CONSTAT_PROJ_DATES.JOB_NUMBER = V_CONSTAT_ACTUAL_DATES.JOB_NUMBER
        INNER JOIN homefront.dbo.V_CONSTAT_BASE_DATES V_CONSTAT_BASE_DATES ON V_CONSTAT_ACTUAL_DATES.JOB_NUMBER = V_CONSTAT_BASE_DATES.JOB_NUMBER
                    AND V_CONSTAT_PROJ_DATES.JOB_NUMBER = V_CONSTAT_BASE_DATES.JOB_NUMBER
        INNER JOIN homefront.dbo.V_CONSTAT_SCH_DATES V_CONSTAT_SCH_DATES ON V_CONSTAT_BASE_DATES.JOB_NUMBER = V_CONSTAT_SCH_DATES.JOB_NUMBER
                    AND V_CONSTAT_PROJ_DATES.JOB_NUMBER = V_CONSTAT_SCH_DATES.JOB_NUMBER
                    AND V_CONSTAT_ACTUAL_DATES.JOB_NUMBER = V_CONSTAT_SCH_DATES.JOB_NUMBER
  WHERE V_CONSTAT_ACTUAL_DATES.AREA_DESC = 'Ancaster Augusta Ph 4(A) Condos'
        AND V_CONSTAT_ACTUAL_DATES.DATE_TO_END >= GETDATE()
  ) AS d

and as a full query:

SELECT (
        SELECT
              CAST('<' + V_CONSTAT_ACTUAL_DATES.JOB_NUMBER + '>' +
              CAST((
                    SELECT (
                                 SELECT
                                       CONVERT(date, d.DATE_TO_END) AS 'closingDate'
                                 FOR xml PATH (''), TYPE
                           )
                         , (
                                 SELECT
                                       DATEDIFF(dd, d.ID67, V_CONSTAT_ACTUAL_DATES.DATE_TO_END) - 1 AS 'DaysOfConstruction'
                                 FOR xml PATH (''), TYPE
                           )
                         , (
                                 SELECT
                                       DATEDIFF(dd, GETDATE(), d.DATE_TO_END) AS 'DaysToClosing'
                                 FOR xml PATH (''), TYPE
                           )
                         , (
                                 SELECT
                                       CASE
                                             WHEN COALESCE(d.IDNOTES2, '') = '' THEN ' '
                                             ELSE d.IDNOTES2
                                       END AS 'notes'
                                 FOR xml PATH (''), TYPE
                           )
                         , (
                                 SELECT
                                       DATEDIFF(dd, d.ID187, d.ID187) AS 'ScheduleVariance'
                                 FOR xml PATH (''), TYPE
                           )
                         , (
                                 SELECT
                                       SortOrder
                                 FROM (
                                       SELECT
                                             d.SortOrder
                                 ) AS SubQuery
                                 FOR xml PATH (''), TYPE
                           )


                    FOR xml PATH ('')
              )
              AS varchar(max)

              )
              + '</' + V_CONSTAT_ACTUAL_DATES.JOB_NUMBER + '>'
              AS xml)
  )
FROM (
  SELECT
        *
      , ROW_NUMBER() OVER (PARTITION BY V_CONSTAT_ACTUAL_DATES.JOB_NUMBER
                            ORDER BY V_CONSTAT_ACTUAL_DATES.DATE_TO_END) AS "SortOrder"
  FROM homefront.dbo.V_CONSTAT_PROJ_DATES V_CONSTAT_PROJ_DATES
        INNER JOIN homefront.dbo.V_CONSTAT_ACTUAL_DATES V_CONSTAT_ACTUAL_DATES ON V_CONSTAT_PROJ_DATES.JOB_NUMBER = V_CONSTAT_ACTUAL_DATES.JOB_NUMBER
        INNER JOIN homefront.dbo.V_CONSTAT_BASE_DATES V_CONSTAT_BASE_DATES ON V_CONSTAT_ACTUAL_DATES.JOB_NUMBER = V_CONSTAT_BASE_DATES.JOB_NUMBER
                    AND V_CONSTAT_PROJ_DATES.JOB_NUMBER = V_CONSTAT_BASE_DATES.JOB_NUMBER
        INNER JOIN homefront.dbo.V_CONSTAT_SCH_DATES V_CONSTAT_SCH_DATES ON V_CONSTAT_BASE_DATES.JOB_NUMBER = V_CONSTAT_SCH_DATES.JOB_NUMBER
                    AND V_CONSTAT_PROJ_DATES.JOB_NUMBER = V_CONSTAT_SCH_DATES.JOB_NUMBER
                    AND V_CONSTAT_ACTUAL_DATES.JOB_NUMBER = V_CONSTAT_SCH_DATES.JOB_NUMBER
  WHERE V_CONSTAT_ACTUAL_DATES.AREA_DESC = 'Ancaster Augusta Ph 4(A) Condos'
        AND V_CONSTAT_ACTUAL_DATES.DATE_TO_END >= GETDATE()
  ) AS d
ORDER BY
      V_CONSTAT_ACTUAL_DATES.DATE_TO_END
FOR xml PATH (''), ROOT ('Root')

Upvotes: 1

Andrey Nadezhdin
Andrey Nadezhdin

Reputation: 126

You can use subquery for your needs. Try something like this:

SELECT SortOrder FROM 
    (SELECT ROW_NUMBER() OVER
        (ORDER BY V_CONSTAT_ACTUAL_DATES.DATE_TO_END)
            AS 'SortOrder') AS SubQuery
FOR XML PATH(''), Type

Edit: After reviewing full query I can recommend you another solution.

Actualy you will have all query logic inside your subselect and ROW_NUMBER() function will work perfectly. The outside part will cover query in XML.

P.S. Little bit more explained:

SELECT (
    SELECT CAST('<'+JOB_NUMBER + '>' +  
        CAST((SELECT
            (SELECT closingDate FOR XML PATH(''), Type),
            (SELECT DaysOfConstruction FOR XML PATH(''), Type),
            (SELECT DaysToClosing FOR XML PATH(''), Type),
            (SELECT notes FOR XML PATH(''), Type),
            (SELECT ScheduleVariance FOR XML PATH(''), Type),
            (SELECT SortOrder FOR XML PATH(''), Type)
        FOR XML PATH('')) AS VARCHAR(MAX))
    + '</'+ JOB_NUMBER + '>' as xml))
FROM 
    (SELECT 
        V_CONSTAT_ACTUAL_DATES.JOB_NUMBER,
        CONVERT(date, V_CONSTAT_ACTUAL_DATES.DATE_TO_END) AS closingDate,
        DATEDIFF(dd, V_CONSTAT_BASE_DATES.ID67,V_CONSTAT_ACTUAL_DATES.DATE_TO_END) - 1 AS DaysOfConstruction,
        DATEDIFF(dd, GETDATE(),V_CONSTAT_ACTUAL_DATES.DATE_TO_END) AS DaysToClosing,
        (CASE WHEN COALESCE(V_CONSTAT_ACTUAL_DATES.IDNOTES2, '') = '' THEN ' ' ELSE V_CONSTAT_ACTUAL_DATES.IDNOTES2 END) AS notes,
        DATEDIFF(dd, V_CONSTAT_BASE_DATES.ID187,V_CONSTAT_PROJ_DATES.ID187) AS ScheduleVariance,
        ROW_NUMBER() OVER (ORDER BY V_CONSTAT_ACTUAL_DATES.DATE_TO_END) AS SortOrder
    FROM homefront.dbo.V_CONSTAT_PROJ_DATES V_CONSTAT_PROJ_DATES
        INNER JOIN homefront.dbo.V_CONSTAT_ACTUAL_DATES V_CONSTAT_ACTUAL_DATES ON V_CONSTAT_PROJ_DATES.JOB_NUMBER=V_CONSTAT_ACTUAL_DATES.JOB_NUMBER 
        INNER JOIN homefront.dbo.V_CONSTAT_BASE_DATES V_CONSTAT_BASE_DATES ON 
            V_CONSTAT_ACTUAL_DATES.JOB_NUMBER=V_CONSTAT_BASE_DATES.JOB_NUMBER AND V_CONSTAT_PROJ_DATES.JOB_NUMBER=V_CONSTAT_BASE_DATES.JOB_NUMBER
        INNER JOIN homefront.dbo.V_CONSTAT_SCH_DATES V_CONSTAT_SCH_DATES ON 
                V_CONSTAT_BASE_DATES.JOB_NUMBER=V_CONSTAT_SCH_DATES.JOB_NUMBER AND V_CONSTAT_PROJ_DATES.JOB_NUMBER=V_CONSTAT_SCH_DATES.JOB_NUMBER 
                AND V_CONSTAT_ACTUAL_DATES.JOB_NUMBER=V_CONSTAT_SCH_DATES.JOB_NUMBER
    WHERE V_CONSTAT_ACTUAL_DATES.AREA_DESC = 'AnCASTer Augusta Ph 4(A) Condos' AND V_CONSTAT_ACTUAL_DATES.DATE_TO_END>=GETDATE()) AS SubQuery
ORDER BY closingDate
FOR XML PATH(''), ROOT('Root')

Upvotes: 1

Lukas Eder
Lukas Eder

Reputation: 221380

You're putting ROW_NUMBER() inside of a scalar subquery with exactly one row:

(SELECT ROW_NUMBER() OVER
    (ORDER BY V_CONSTAT_ACTUAL_DATES.DATE_TO_END)
        AS 'SortOrder')

You're probably thinking that the ORDER BY clause (which references a column from the outer query) makes any difference to the calculation of ROW_NUMBER() here. It doesn't. This subquery returns one row and the only reasonable ROW_NUMBER for that row is always 1.

The solution is to delay conversion of your results to XML for as long as possible and just select your data ordinarily, with the ROW_NUMBER() calculation at the top level SELECT.

Upvotes: 5

Related Questions