CODEWITHSUNDEEP
sqlsql-servert-sql
user1947702
user1947702

Reputation: 154

(ORDER BY CASE WHEN) ordering by subquery

I need to order my results by int column ascending, but I want to get only rows with numbers (0...10000) but default ordering gives me rows with null values for this column before numbers. I googled solution which set rows with null into the end of ordering (after all numbers) it looks like 

SELECT ProductName
FROM Products
ORDER BY
  CASE WHEN Position is null THEN 1 ELSE 0 END,
  Position

So I my query looks like: 

SELECT  c.CompanyId, c.CompanyName, c.CompanyCategoryId, cc.CompanyCategoryName, c.HQCountryISO, c.CrunchBaseUrl,c.AngelListUrl, 
(SELECT MAX(mf.NumLikes) FROM MeasurementFacebook mf
JOIN FacebookAccount f ON f.CompanyId = c.CompanyId
WHERE f.FacebookAccountId in (mf.FacebookAccountId)) as Likes,
(SELECT MAX(mt.NumFollowers) FROM MeasurementTwitter mt
JOIN TwitterAccount t ON t.CompanyId = c.CompanyId
WHERE t.TwitterAccountId in (mt.TwitterAccountId)) as Followers,
(SELECT MAX(ma.AlexaRanking) FROM MeasurementAlexa ma
JOIN Website w ON w.CompanyId = c.CompanyId
WHERE w.WebsiteId in (ma.WebsiteId)) as AlexaRank
FROM Company c 
JOIN CompanyCategory cc ON c.CompanyCategoryId = cc.CompanyCategoryId 
WHERE c.HQCountryISO =  'FRA' 
ORDER BY CASE  WHEN AlexaRank IS NULL THEN 1 ELSE 0 END, AlexaRank 
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

As you can see, AlexaRank is the result of third subquery, and I want to order result by this column. But I have an error which says: 

Msg 207, Level 16, State 1, Line 14
Invalid column name 'AlexaRank'.

What I'm doing wrong? Thanks

Upvotes: 1

Views: 580

Answers (2)

voglster
voglster

Reputation: 833

Very inefficient code but you could do something like the following. Basically wrap your initial query in a common table expression so you don't need to rewrite your 3rd sub-select in your order by.

SELECT * FROM (
SELECT c.companyid, 
   c.companyname, 
   c.companycategoryid, 
   cc.companycategoryname, 
   c.hqcountryiso, 
   c.crunchbaseurl, 
   c.angellisturl, 
   (SELECT Max(mf.numlikes) 
    FROM   measurementfacebook mf 
           JOIN facebookaccount f 
             ON f.companyid = c.companyid 
    WHERE  f.facebookaccountid IN ( mf.facebookaccountid )) AS Likes, 
   (SELECT Max(mt.numfollowers) 
    FROM   measurementtwitter mt 
           JOIN twitteraccount t 
             ON t.companyid = c.companyid 
    WHERE  t.twitteraccountid IN ( mt.twitteraccountid ))   AS Followers, 
   (SELECT Max(ma.alexaranking) 
    FROM   measurementalexa ma 
           JOIN website w 
             ON w.companyid = c.companyid 
    WHERE  w.websiteid IN ( ma.websiteid ))                 AS AlexaRank 
FROM   company c 
   JOIN companycategory cc 
     ON c.companycategoryid = cc.companycategoryid 
WHERE  c.hqcountryiso = 'FRA' ) Q
ORDER  BY CASE 
        WHEN Q.AlexaRank IS NULL THEN 1 
        ELSE 0 
      END, 
      Q.AlexaRank

Upvotes: 0

Hart CO
Hart CO

Reputation: 34774

While you can use an alias in the ORDER BY clause, you can't use an alias in an expression, easiest solution is to plop it in a cte/subquery:

;WITH cte AS (SELECT  c.CompanyId
                      , c.CompanyName
                      , c.CompanyCategoryId
                      , cc.CompanyCategoryName
                      , c.HQCountryISO
                      , c.CrunchBaseUrl
                      ,c.AngelListUrl
                      ,(SELECT MAX(mf.NumLikes) 
                        FROM MeasurementFacebook mf
                        JOIN FacebookAccount f ON f.CompanyId = c.CompanyId
                        WHERE f.FacebookAccountId in (mf.FacebookAccountId)) as Likes
                      ,(SELECT MAX(mt.NumFollowers) 
                        FROM MeasurementTwitter mt
                        JOIN TwitterAccount t ON t.CompanyId = c.CompanyId
                        WHERE t.TwitterAccountId in (mt.TwitterAccountId)) as Followers
                      ,(SELECT MAX(ma.AlexaRanking) 
                        FROM MeasurementAlexa ma
                        JOIN Website w ON w.CompanyId = c.CompanyId
                        WHERE w.WebsiteId in (ma.WebsiteId)) as AlexaRank
                FROM Company c 
                JOIN CompanyCategory cc ON c.CompanyCategoryId = cc.CompanyCategoryId 
                WHERE c.HQCountryISO =  'FRA')
SELECT * 
FROM  cte
ORDER BY CASE WHEN AlexaRank IS NULL THEN 1 ELSE 0 END, AlexaRank 
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

Upvotes: 3

Related Questions