CODEWITHSUNDEEP
sqlsql-servert-sql
DotnetSparrow
DotnetSparrow

Reputation: 27996

Ordering the results of CTE in SQL

I have following SQL:

;WITH CTE 
     AS (SELECT AL.*, 
                RV.FORENAME, 
                RV.SURNAME, 
                RV.USERNAME                  AS RegistrantUsername, 
                E.FORENAME                   AS EmployeeForename, 
                E.SURNAME                    AS EmployeeSurname, 
                U.USERNAME, 
                CASE 
                  WHEN @Language = 2 THEN C.NAMELANG2 
                  ELSE C.NAMELANG1 
                END                          AS CompanyName, 
                CASE 
                  WHEN VC.COMPANYID IS NULL THEN 
                    CASE 
                      WHEN @Language = 2 THEN V.COMPANYNAMELANG2 
                      ELSE V.COMPANYNAMELANG1 
                    END 
                  ELSE 
                    CASE 
                      WHEN @Language = 2 THEN VC.NAMELANG2 
                      ELSE VC.NAMELANG1 
                    END 
                END                          AS VacancyCompanyName, 
                CASE 
                  WHEN @Language = 2 THEN V.JOBTITLELANG2 
                  ELSE V.JOBTITLELANG1 
                END                          AS JobTitle, 
                ROW_NUMBER() 
                  OVER( 
                    PARTITION BY AL.REGISTRANTID, AL.COMPANYID 
                    ORDER BY ACTIONDATE ASC) AS RN 
         FROM   DBO.HR_ACTIONLOG AL 
                LEFT OUTER JOIN DBO.REGISTRANTSLISTVIEW RV 
                             ON AL.REGISTRANTID = RV.REGISTRANTID 
                LEFT OUTER JOIN DBO.HR_EMPLOYEES E 
                             ON AL.EMPLOYEEID = E.EMPLOYEEID 
                LEFT OUTER JOIN DBO.HR_USERS U 
                             ON AL.USERID = U.USERID 
                LEFT OUTER JOIN DBO.HR_COMPANIES C 
                             ON AL.COMPANYID = C.COMPANYID 
                LEFT OUTER JOIN DBO.HR_VACANCIES V 
                             ON AL.VACANCYID = V.VACANCYID 
                LEFT OUTER JOIN DBO.HR_COMPANIES VC 
                             ON V.COMPANYID = VC.COMPANYID 
         WHERE  ( @Action IS NULL 
                   OR AL.ACTION = @Action ) 
                AND ( @DateFrom IS NULL 
                       OR DBO.DATEONLY(AL.ACTIONDATE) >= 
                    DBO.DATEONLY(@DateFrom) ) 
                AND ( @DateTo IS NULL 
                       OR DBO.DATEONLY(AL.ACTIONDATE) <= DBO.DATEONLY(@DateTo) ) 
                AND ( @CompanyID IS NULL 
                       OR AL.COMPANYID = @CompanyID ) 
                AND ( @RegistrantID IS NULL 
                       OR AL.REGISTRANTID = @RegistrantID ) 
                AND ( @VacancyID IS NULL 
                       OR AL.VACANCYID = @VacancyID ) 
        --ORDER BY AL.ActionDate DESC 
        ) 
SELECT * 
FROM   CTE 
WHERE  RN = 1;

It returns first element from the group based on actiondate which is fine but the returned result is not ordered by date means returns each groups first record but the this collection of first records is not ordered by action date. I tried ORDER BY AL.ActionDate DESC in CTE but it gives error:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

Upvotes: 1

Views: 1769

Answers (5)

Gidil
Gidil

Reputation: 4137

You have a ROW_NUMBER function inside the CTE and the query only takes the row with RN=1.
If I understand the problem correctly, just change the ORDER BY clause in the ROW_NUMBER function and you should get the results you're looking for.

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239654

Yes, the only ORDER BY that affects the order in which the results are returned is one placed on the outermost SELECT:

SELECT *
FROM CTE
WHERE RN = 1
ORDER BY ActionDate DESC

Upvotes: 2

Devart
Devart

Reputation: 121912

Try this one -

;WITH CTE AS
(
    SELECT  ...
    FROM dbo.hr_ActionLog AL
    LEFT JOIN ...
    WHERE AL.[Action] = ISNULL(@Action, AL.[Action])
        AND dbo.DateOnly(AL.ActionDate) BETWEEN 
            dbo.DateOnly(ISNULL(@DateFrom, AL.ActionDate))
            AND
            dbo.DateOnly(ISNULL(@DateTo, '30000101'))
        AND AL.CompanyID = ISNULL(@CompanyID, AL.CompanyID)
        AND AL.RegistrantID = ISNULL(@RegistrantID, AL.RegistrantID)
        AND AL.VacancyID = ISNULL(@VacancyID, AL.VacancyID)
)
SELECT *
FROM CTE
WHERE RN = 1
ORDER BY AL.ActionDate DESC;

Upvotes: 2

John Woo
John Woo

Reputation: 263713

you do the ordering outside CTE

WITH CTE
AS
{
 ....
}
SELECT *
FROM CTE
WHERE RN = 1
ORDER BY ActionDate DESC

Upvotes: 2

roman
roman

Reputation: 117370

Move order by clause outside of cte:

WITH CTE AS 
(
    ...
)
SELECT *
FROM CTE
WHERE RN = 1;
ORDER BY ActionDate DESC

Upvotes: 2

Related Questions