Sorting so 0 is at the bottom

[Philip Mette]

Is there a way to sort records the following way.

Original Table:

KeyField Column 1
1 0
2 500
3 200
4 300
5 0
To this sorted table

KeyField Column 1
3 200
4 300
2 500
1 0
5 0
so that the numbers in column 1 are sorted asc but it treats a 0 as
the largest number. We need the first number greater than 0 to be
first in the table.

[Simon Hayes]

"Philip Mette" <ph******@msn.com> wrote in message
news:96**************************@posting.google.c om...

Is there a way to sort records the following way.

Original Table:

KeyField Column 1
1 0
2 500
3 200
4 300
5 0
To this sorted table

KeyField Column 1
3 200
4 300
2 500
1 0
5 0
so that the numbers in column 1 are sorted asc but it treats a 0 as
the largest number. We need the first number greater than 0 to be
first in the table.

This is one way:

select *
from dbo.MyTable
order by case Column1 when 0 then 1 else 0 end, Column1

Simon

[Hugo Kornelis]

On 25 Aug 2004 08:51:59 -0700, Philip Mette wrote:

Is there a way to sort records the following way.

Original Table:

KeyField Column 1
1 0
2 500
3 200
4 300
5 0
To this sorted table

KeyField Column 1
3 200
4 300
2 500
1 0
5 0
so that the numbers in column 1 are sorted asc but it treats a 0 as
the largest number. We need the first number greater than 0 to be
first in the table.

Hi Philip,

ORDER BY CASE Column1
WHEN 0 THEN 0
ELSE 1
END, Column1

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Hugo Kornelis]

On Wed, 25 Aug 2004 20:59:31 +0200, Hugo Kornelis wrote:

Hi Philip,

ORDER BY CASE Column1
WHEN 0 THEN 0
ELSE 1
END, Column1

Best, Hugo

Oops. Last line shopuld have been
END DESC, Column1

Of course, Simon's suggestion works okay as well.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Philip Mette]

Hugo Kornelis <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:<h1********************************@4ax.com>. ..

On Wed, 25 Aug 2004 20:59:31 +0200, Hugo Kornelis wrote:

Hi Philip,

ORDER BY CASE Column1
WHEN 0 THEN 0
ELSE 1
END, Column1

Best, Hugo

Oops. Last line shopuld have been
END DESC, Column1

Of course, Simon's suggestion works okay as well.

Best, Hugo

Thanks Hugo works great! How is this working? Are you setting the
order index to 0 when 0 and all other numbers index to 1? I am sort of
a newbie.

[Hugo Kornelis]

On 26 Aug 2004 08:07:04 -0700, Philip Mette wrote:

Hugo Kornelis <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:<h1********************************@4ax.com>. ..

On Wed, 25 Aug 2004 20:59:31 +0200, Hugo Kornelis wrote:

>Hi Philip,
>
>ORDER BY CASE Column1
> WHEN 0 THEN 0
> ELSE 1
> END, Column1
>
>Best, Hugo

Oops. Last line shopuld have been
END DESC, Column1

Of course, Simon's suggestion works okay as well.

Best, Hugo

Thanks Hugo works great! How is this working? Are you setting the
order index to 0 when 0 and all other numbers index to 1? I am sort of
a newbie.

Hi Philip,

I use two sort columns. The first is
CASE Column1
WHEN 0 THEN 0
ELSE 1
END
This CASE expression will return 0 is Column1 is zero; it returns 1 in all
other cases (copy and paste this expression in the SELECT list as well if
you want to see it in action). I specified descending sort on this column
(the keyword DESC that I forgot in my earlier post), so all rows with 1
for this expression (ie all rows with Column1 <> 0) go first and all rows
with 0 for this CASE expression (ie all rows Column1 = 0) come last.

The second sort column is Column1 (the comma after DESC indicates that
another sort column follows). This one sorts in the default ASCending
order. Since it's the second sort column, it will be used as a tie
breaker. Remember, the first sort column was 1 for all rows with Column1
<> 0; all these rows go first, but their ordering withing this group will
be an ascending order based on Column1. The second group will be the
columns with 0 for the CASE expression; these all have Column1 equal to 0
so their ordering within the group is still undefined.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)