Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Conditional Sorting

תשובה Conditional Sorting

  • Monday, February 20, 2012 5:25 AM
     
      Has Code
    Sign In to Vote
    0

    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!

     

All Replies

  • Monday, February 20, 2012 5:38 AM
     
     
    Sign In to Vote
    0

    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:42 AM
     
      Has Code
    Sign In to Vote
    0

    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 12:20 PM
     
     Answered Has Code
    Sign In to Vote
    0

    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 2:23 PM
    Answerer
     
      Has Code
    Sign In to Vote
    0

    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 3:55 PM
     
      Has Code
    Sign In to Vote
    0

    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:57 PM
     
     
    Sign In to Vote
    0

    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 4:07 PM
    Moderator
     
     
    Sign In to Vote
    0

    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:10 PM
     
      Has Code
    Sign In to Vote
    0

    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:20 PM
    Moderator
     
      Has Code
    Sign In to Vote
    0

    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:33 PM
     
      Has Code
    Sign In to Vote
    0

    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:35 PM
    Moderator
     
     
    Sign In to Vote
    0
    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

     
  • Tuesday, February 21, 2012 6:16 AM
    Answerer
     
      Has Code
    Sign In to Vote
    0

    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