none
Conditional Sorting

    Question

  • Quick question...

    I currently have the following query.

    SELECT     
        DISTINCT(AddressStreet), 
        COUNT(CASE WHEN DoorCurrentAnswer = '' THEN 1 ELSE NULL END) AS [HouseholdsRemaining],
        COUNT(AddressStreet) AS [HouseholdCount]
    FROM         
    	Households
    GROUP BY 
    	AddressStreet
    ORDER BY 
    	AddressStreet

    It will return some results like this....

    AddressStreet HouseholdsRemaining HouseholdCount
    Alpha 0 7
    Bravo 7 9
    Charlie 2 10
    Delta 0 8

    I need to modify my order by clause to sort at the top first any results where "HouseholdsRemaining" > 0, then below that, any results where "HouseholdsRemaining" = 0. Of course, they should be alphabetical within these two groups.

    The Modified Results would look like this...

    AddressStreet HouseholdsRemaining HouseholdCount
    Alpha 0 7
    Delta 0 8
    Bravo 7 9
    Charlie 2 10

    Thanks!

    Monday, February 20, 2012 5:25 AM

Answers

  • Try the following (Untested, but, should point you in the right direction):

    SELECT AddressStreet
          , HouseholdsRemaining
          , HouseHoldCount
    FROM
         (SELECT     
            DISTINCT(AddressStreet), 
            COUNT(CASE WHEN DoorCurrentAnswer = '' THEN 1 ELSE NULL END) AS [HouseholdsRemaining],
            COUNT(AddressStreet) AS [HouseholdCount]
          FROM         
    	Households
          GROUP BY 
    	AddressStreet
    )
    ORDER BY 
        CASE HouseHoldsRemaining
            WHEN 0 THEN 999999999
            ELSE HouseholdsRemaining
        END DESC,
        AddressStreet


    Dave Frommer - BI Architect - Independent

    Monday, February 20, 2012 12:20 PM

All replies

  • You need to use CASE statement with ORDER BY here, check this:

    SELECT
    DISTINCT(AddressStreet),
    COUNT(CASE WHEN DoorCurrentAnswer = '' THEN 1 ELSE NULL END) AS [HouseholdsRemaining],
    COUNT(AddressStreet) AS [HouseholdCount]
    FROM
    Households
    GROUP BY
    AddressStreet
    ORDER BY
    CASE WHEN HouseholdsRemaining=0 THEN '0' ELSE AddressStreet END


    Check more on using such cases at following blog post for ORDER BY CASE.


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011


    Monday, February 20, 2012 5:38 AM
  • Check this template.

    Declare @OrderBy int
    Set @OrderBy = 1
    
    Selecct Col1, Col2 
    from Table1
    Order By
    CASE WHEN @OrderBy = 0 THEN Col1 End ASC,
    CASE WHEN @OrderBy = 1 THEN Col2 End DESC

    You even use, IF statement to write two separate queries with it's own order by clause.

    Hope this answers your question.


    Planet Earth is at risk. Global warming is on a high tide.
    Take Responsibility. Plant Trees. Keep your City Clean and Green.

    Mark all Helping Posts and Close your Threads. Keep the Forum Green.
     - Arun Kumar Allu

    Monday, February 20, 2012 5:42 AM
  • Try the following (Untested, but, should point you in the right direction):

    SELECT AddressStreet
          , HouseholdsRemaining
          , HouseHoldCount
    FROM
         (SELECT     
            DISTINCT(AddressStreet), 
            COUNT(CASE WHEN DoorCurrentAnswer = '' THEN 1 ELSE NULL END) AS [HouseholdsRemaining],
            COUNT(AddressStreet) AS [HouseholdCount]
          FROM         
    	Households
          GROUP BY 
    	AddressStreet
    )
    ORDER BY 
        CASE HouseHoldsRemaining
            WHEN 0 THEN 999999999
            ELSE HouseholdsRemaining
        END DESC,
        AddressStreet


    Dave Frommer - BI Architect - Independent

    Monday, February 20, 2012 12:20 PM
  • HI imdan !

    You may get the desired output using below query;

    SELECT     
        DISTINCT(AddressStreet), 
        COUNT(CASE WHEN DoorCurrentAnswer = '' THEN 1 ELSE NULL END) AS [HouseholdsRemaining],
        COUNT(AddressStreet) AS [HouseholdCount]
    FROM         
    	Households
    GROUP BY 
    	AddressStreet
    ORDER BY 
    	CASE WHEN HouseholdsRemaining > 0 THEN 0 ELSE 1 END
    	, AddressStreet



    Please let me know if this doesn’t work for you. Hope I have answered you correctly.

    Thanks, Hasham

    Monday, February 20, 2012 2:23 PM
    Answerer
  • When I try to use a case in the order by clause, I get the following error.

    Msg 207, Level 16, State 1, Line 37
    Invalid column name 'HouseholdsRemaining'.
    Msg 145, Level 15, State 1, Line 37
    ORDER BY items must appear in the select list if SELECT DISTINCT is specified.


    This error occurs when I use either manub22's query...

    SELECT
    DISTINCT(AddressStreet),
    COUNT(CASE WHEN DoorCurrentAnswer = '' THEN 1 ELSE NULL END) AS [HouseholdsRemaining],
    COUNT(AddressStreet) AS [HouseholdCount]
    FROM
    Households
    GROUP BY
    AddressStreet
    ORDER BY
    CASE WHEN HouseholdsRemaining=0 THEN '0' ELSE AddressStreet END

    or Hasham Niaz's query...

    SELECT     
        DISTINCT(AddressStreet), 
        COUNT(CASE WHEN DoorCurrentAnswer = '' THEN 1 ELSE NULL END) AS [HouseholdsRemaining],
        COUNT(AddressStreet) AS [HouseholdCount]
    FROM         
    	Households
    GROUP BY 
    	AddressStreet
    ORDER BY 
    	CASE WHEN HouseholdsRemaining > 0 THEN 0 ELSE 1 END
    	, AddressStreet

    Monday, February 20, 2012 3:55 PM
  • That is because That is an Alias in the Select clause which is not processed until the end. That is why I wrapped your query as a sub-query.  Did you try my solution?


    Dave Frommer - BI Architect - Independent

    Monday, February 20, 2012 3:57 PM
  • Brad Schulz has a blog post on this very topic. It is very entertaining, so I suggest you to read it

    The Troll's Puzzle: A SQL Fable


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, February 20, 2012 4:07 PM
    Moderator
  • That is because That is an Alias in the Select clause which is not processed until the end. That is why I wrapped your query as a sub-query.  Did you try my solution?


    Dave Frommer - BI Architect - Independent

    Yes, but it has a syntax error I cannot pinpoint.

    SELECT AddressStreet
          , HouseholdsRemaining
          , HouseHoldCount
    FROM
         (SELECT     
            DISTINCT(AddressStreet), 
            COUNT(CASE WHEN DoorCurrentAnswer = '' THEN 1 ELSE NULL END) AS [HouseholdsRemaining],
            COUNT(AddressStreet) AS [HouseholdCount]
          FROM         
    	Households
          GROUP BY 
    	AddressStreet
    )
    ORDER BY 
        CASE HouseHoldsRemaining
            WHEN 0 THEN 999999999
            ELSE HouseholdsRemaining
        END DESC,
        AddressStreet

    Yields the error...

    Msg 156, Level 15, State 1, Line 14
    Incorrect syntax near the keyword 'ORDER'.


    I tried simplifying the query like this....

    SELECT AddressStreet
          , HouseholdsRemaining
          , HouseHoldCount
    FROM
         (SELECT 'X' as [AddressStreet], 'y' as [HouseholdsRemaining], 'z' as [HouseholdCount])
    ORDER BY 
        CASE [HouseholdsRemaining]
            WHEN 0 THEN 999999999
            ELSE HouseholdsRemaining
        END DESC,
        AddressStreet

    But got the same error only on a different line number.

    Monday, February 20, 2012 4:10 PM
  • You're missing alias for derived table, try

    SELECT AddressStreet
          , HouseholdsRemaining
          , HouseHoldCount
    FROM
         (SELECT     
            AddressStreet, 
            COUNT(CASE WHEN DoorCurrentAnswer = '' THEN 1 ELSE NULL END) AS [HouseholdsRemaining],
            COUNT(AddressStreet) AS [HouseholdCount]
          FROM         
    	Households
          GROUP BY 
    	AddressStreet
    ) X -- this is the missing alias
    ORDER BY 
        CASE HouseHoldsRemaining
            WHEN 0 THEN 999999999
            ELSE HouseholdsRemaining
        END DESC,
        AddressStreet


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, February 20, 2012 4:20 PM
    Moderator
  • You're missing alias for derived table, try

    SELECT AddressStreet
          , HouseholdsRemaining
          , HouseHoldCount
    FROM
         (SELECT     
            AddressStreet, 
            COUNT(CASE WHEN DoorCurrentAnswer = '' THEN 1 ELSE NULL END) AS [HouseholdsRemaining],
            COUNT(AddressStreet) AS [HouseholdCount]
          FROM         
    	Households
          GROUP BY 
    	AddressStreet
    ) X -- this is the missing alias
    ORDER BY 
        CASE HouseHoldsRemaining
            WHEN 0 THEN 999999999
            ELSE HouseholdsRemaining
        END DESC,
        AddressStreet


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    I modified the query a little bit with this, which is now giving the desired results...

    SELECT AddressStreet
          , HouseholdsRemaining
          , HouseHoldCount
    FROM
    	(SELECT     
    		DISTINCT(AddressStreet), 
    		COUNT(CASE WHEN DoorCurrentAnswer = '' THEN 1 ELSE NULL END) AS [HouseholdsRemaining],
    		COUNT(AddressStreet) AS [HouseholdCount]
    	FROM         
    		Households
    	GROUP BY 
    		AddressStreet) [SubQuery]
    ORDER BY 
    	CASE HouseholdsRemaining
    	    WHEN 0 THEN 1
    	    ELSE 0
    	END ASC, 
    	AddressStreet

    As a matter of curiosity, is it possible to acheive the same results without using the subquery?

    Monday, February 20, 2012 4:33 PM
  • No, not in this case. I believe using derived table will be the only solution for your case. Check the blog I recommended, it explains it very well.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, February 20, 2012 4:35 PM
    Moderator
  • HI imdan !

    You may get the desired output using below query;

    --DROP TABLE        #HouseHolds
    CREATE TABLE    #HouseHolds(AddressStreet VARCHAR(10),DoorCurrentAnswer VARCHAR(1))
    INSERT INTO        #HouseHolds
    SELECT    'Alpha','Y' UNION ALL
    SELECT    'Alpha','N' UNION ALL
    SELECT    'Alpha','Y' UNION ALL
    SELECT    'Bravo','Y' UNION ALL
    SELECT    'Bravo','Y' UNION ALL
    SELECT    'Bravo','' UNION ALL
    SELECT    'Charlie','' UNION ALL
    SELECT    'Charlie','' UNION ALL
    SELECT    'Charlie','N'  UNION ALL
    SELECT    'Delta','N'  UNION ALL
    SELECT    'Delta','N'  UNION ALL
    SELECT    'Delta','N'
    
    SELECT     
        AddressStreet,
        COUNT(CASE WHEN DoorCurrentAnswer = '' THEN 1 ELSE NULL END) AS [HouseholdsRemaining],
        COUNT(AddressStreet) AS [HouseholdCount]
    FROM         
        #Households
    GROUP BY
        AddressStreet
    ORDER BY
        CASE WHEN COUNT(CASE WHEN DoorCurrentAnswer = '' THEN 1 ELSE NULL END) > 0 THEN 1 ELSE 0 END
        , AddressStreet

    Note : You can't use derived column names in ORDER BY Clause so you have to move your derived column condition in ORDER BY Clause as well.

    @Naomi : I think its possible to do it without using a derived table.

    Please let me know if this doesn’t work for you. Hope I have answered you correctly.

    Thanks, Hasham

    Tuesday, February 21, 2012 6:16 AM
    Answerer