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

How to do the sorting sequence in numbering order

dear folks,
I would like to know that how to do the number sorting in numbering sequence ?
In order perform a sort you need to use the ORDER BY clause. If the datatype
is a numeric datatype the sort will be performed as a numeric sort ie. 3
comes before 21. However if the dataype is a character datatype a character
sort will be performed ie. 21 comes before 3. Below is an illustration of
this:
CREATE TABLE nums
(
i INT,
data VARCHAR(20)
)
INSERT nums SELECT 1, 'a'
INSERT nums SELECT 3, 'b'
INSERT nums SELECT 21, 'c'
INSERT nums SELECT 2, 'd'
SELECT *
FROM nums
ORDER BY i
Returns :
i data
-- --
1 a
2 d
3 b
21 c
(4 row(s) affected)
CREATE TABLE chars
(
i VARCHAR(10),
data VARCHAR(20)
)
INSERT chars SELECT '1', 'a'
INSERT chars SELECT '3', 'b'
INSERT chars SELECT '21', 'c'
INSERT chars SELECT '2', 'd'
SELECT *
FROM chars
ORDER BY i
Returns :
i data
-- --
1 a
2 d
21 c
3 b
(4 row(s) affected)
If the column your are attempting sort stores the number as a character then
you will need to convert the number to a numeric datatype:
ie.
SELECT *
FROM chars
ORDER BY CONVERT(INT, i)
i data
-- --
1 a
2 d
3 b
21 c
(4 row(s) affected)
- Peter Ward
WARDY IT Solutions

How to do the sorting sequence in numbering order

dear folks,
I would like to know that how to do the number sorting in numbering sequence
?In order perform a sort you need to use the ORDER BY clause. If the datatyp
e
is a numeric datatype the sort will be performed as a numeric sort ie. 3
comes before 21. However if the dataype is a character datatype a character
sort will be performed ie. 21 comes before 3. Below is an illustration of
this:
CREATE TABLE nums
(
i INT,
data VARCHAR(20)
)
INSERT nums SELECT 1, 'a'
INSERT nums SELECT 3, 'b'
INSERT nums SELECT 21, 'c'
INSERT nums SELECT 2, 'd'
SELECT *
FROM nums
ORDER BY i
Returns :
i data
-- --
1 a
2 d
3 b
21 c
(4 row(s) affected)
CREATE TABLE chars
(
i VARCHAR(10),
data VARCHAR(20)
)
INSERT chars SELECT '1', 'a'
INSERT chars SELECT '3', 'b'
INSERT chars SELECT '21', 'c'
INSERT chars SELECT '2', 'd'
SELECT *
FROM chars
ORDER BY i
Returns :
i data
-- --
1 a
2 d
21 c
3 b
(4 row(s) affected)
If the column your are attempting sort stores the number as a character then
you will need to convert the number to a numeric datatype:
ie.
SELECT *
FROM chars
ORDER BY CONVERT(INT, i)
i data
-- --
1 a
2 d
3 b
21 c
(4 row(s) affected)
- Peter Ward
WARDY IT Solutions

How to do the sorting sequence in numbering order

dear folks,
I would like to know that how to do the number sorting in numbering sequence ?In order perform a sort you need to use the ORDER BY clause. If the datatype
is a numeric datatype the sort will be performed as a numeric sort ie. 3
comes before 21. However if the dataype is a character datatype a character
sort will be performed ie. 21 comes before 3. Below is an illustration of
this:
CREATE TABLE nums
(
i INT,
data VARCHAR(20)
)
INSERT nums SELECT 1, 'a'
INSERT nums SELECT 3, 'b'
INSERT nums SELECT 21, 'c'
INSERT nums SELECT 2, 'd'
SELECT *
FROM nums
ORDER BY i
Returns :
i data
-- --
1 a
2 d
3 b
21 c
(4 row(s) affected)
CREATE TABLE chars
(
i VARCHAR(10),
data VARCHAR(20)
)
INSERT chars SELECT '1', 'a'
INSERT chars SELECT '3', 'b'
INSERT chars SELECT '21', 'c'
INSERT chars SELECT '2', 'd'
SELECT *
FROM chars
ORDER BY i
Returns :
i data
-- --
1 a
2 d
21 c
3 b
(4 row(s) affected)
If the column your are attempting sort stores the number as a character then
you will need to convert the number to a numeric datatype:
ie.
SELECT *
FROM chars
ORDER BY CONVERT(INT, i)
i data
-- --
1 a
2 d
3 b
21 c
(4 row(s) affected)
- Peter Ward
WARDY IT Solutions

how to do interactive sorting in sql reporting services 2000

I'm trying to configure a report to sort when a Field(colunm) header is
clicked. I have know idea how to do this. Any help appreciated.
Thanks,
JimHii Jim,
This a new feature of RS 2005, which can be set by using the UserSort
properties on a textbox. For example, select a column header textbox
on your report, then right click and select properties. On the
Interactive Sort tab, check the box to enable the sort and set the
specific expression and scope for the sort.
I'm not aware of any similar feature in RS 2000, but you could use
parameter(s) to prompt a user at report run time. Not interactive, but
at least flexible.
HTH
Matt A
Reporting Services Newsletter at www.reportarchitex.com
Jims wrote:
> I'm trying to configure a report to sort when a Field(colunm) header is
> clicked. I have know idea how to do this. Any help appreciated.
> Thanks,
> Jim