How to exclude zero values when sorting?

I have a datagrid with a "sort" field I want to use to sort the rows in ascending order. However, I want values with a 0 or NULL value to be displayed last. I can't figure out how to do a sort (preferably in the SQL) that returns the empty values last. Is this possible?This isn't pretty but why don't you give it a go:
SELECT * FROM mytable WHERE myfield > 0 ORDER BY myfield ASC
UNION SELECT * FROM mytable WHERE myfield = 0

Regards
Fredr!k|||Unfortunately, this isn't valid syntax becuase ORDER BY must be at the end of the query. I get "Incorrect syntax near the keyword 'UNION'." when I try

SELECT * FROM Photo WHERE PhotoOrder > 0 ORDER BY PhotoOrder ASC UNION SELECT * FROM Photo WHERE PhotoOrder = 0|||To use the UNION operator in SQL Server all your Data types must be the same and the same order in both tables, but UNION is restrictive because it performs an Implicit DISTINCT by eliminating DUPLICATES. So if eliminating duplicates is not important try UNION ALL, if it still fails the it is INNER JOIN if both tables are equal or OUTER JOIN if they are not equal. Hope this helps.

Kind regards,
Gift Peddie|||I'm confused - how is this relevant to my question?|||I got what I wanted with
"ORDER BY IsNull(PhotoORDER, 1000)"

Unfortunately, zeroes will still sort first, but I can NULLify them on data entry|||You might try:

ORDER BY
 CASE WHEN ISNULL(PhotoOrder,0) = 0 THEN 2 ELSE 1 END,
 CASE WHEN ISNULL(PhotoOrder,0) <> 0 THEN PhotoOrder

Terri|||I was only replying your UNION error not your original post. I will try to be clear in the future.

Kind regards,
Gift Peddiesql