Conditional Sorting

Conditional Sorting

• Monday, February 20, 2012 5:25 AM

Quick question...

I currently have the following query.

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

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

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

SELECT
COUNT(CASE WHEN DoorCurrentAnswer = '' THEN 1 ELSE NULL END) AS [HouseholdsRemaining],
FROM
Households
GROUP BY
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

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.

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

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

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

Dave Frommer - BI Architect - Independent

• Monday, February 20, 2012 2:23 PM

HI imdan !

You may get the desired output using below query;

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

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

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
COUNT(CASE WHEN DoorCurrentAnswer = '' THEN 1 ELSE NULL END) AS [HouseholdsRemaining],
FROM
Households
GROUP BY
ORDER BY
CASE WHEN HouseholdsRemaining=0 THEN '0' ELSE AddressStreet END```

or Hasham Niaz's query...

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

• Monday, February 20, 2012 3:57 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 4:07 PM
Moderator

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

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
COUNT(CASE WHEN DoorCurrentAnswer = '' THEN 1 ELSE NULL END) AS [HouseholdsRemaining],
FROM
Households
GROUP BY
)
ORDER BY
CASE HouseHoldsRemaining
WHEN 0 THEN 999999999
ELSE HouseholdsRemaining
END DESC,

Yields the error...

`Msg 156, Level 15, State 1, Line 14Incorrect 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,

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

• Monday, February 20, 2012 4:20 PM
Moderator

You're missing alias for derived table, try

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

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

My blog

• Monday, February 20, 2012 4:33 PM

You're missing alias for derived table, try

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

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
COUNT(CASE WHEN DoorCurrentAnswer = '' THEN 1 ELSE NULL END) AS [HouseholdsRemaining],
FROM
Households
GROUP BY
ORDER BY
CASE HouseholdsRemaining
WHEN 0 THEN 1
ELSE 0
END ASC,

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

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

HI imdan !

You may get the desired output using below query;

```--DROP TABLE        #HouseHolds
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
COUNT(CASE WHEN DoorCurrentAnswer = '' THEN 1 ELSE NULL END) AS [HouseholdsRemaining],
FROM
#Households
GROUP BY