Is it possible to use both order by and where in a single query

Question

I have created a table. In one field, I have a priority of that record (between 1-9). I didn't set priority for all the records, so for some records, it will remain null.

When displaying these records in my HTML page, I just check those priorities -- if the priority exists, then I will display as it is, if it's null, then I will display it as the lowest priority of '10' (just for display).

The problem occurs while sorting the table. If I try to sort(DESC) the table, it displays the 10 in the first row and later continues perfectly (1,2,....).

How to solve this?

Is it possible to display the priorities first and later continue with the null values?

Answer 1

Here's a short sample that shows how to convert NULL's into a value and then sort on it..

create table test
(
    priority int null,
    detail varchar(10)
)

insert into test values (1, 'one')
insert into test values (3, 'three')
insert into test values (8, 'eight')
insert into test values (9, 'nine')
insert into test values (null, 'ten')

select ISNULL(priority, 10), detail from test order by ISNULL(priority, 10)

The key is ISNULL'ing the null'able value field to convert NULL's to the value (10) that you want.