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

Question

Hi friends,

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.

Answer 2

There are a number of ways to solve it.

1/ Change the data so that 10 is actually in the table (rather than NULL):

update table TBL set FLD = 10 where FLD is null;

2/ Modify your query to return different values for NULL:

select FLD1, FLD2, case where FLD3 is null then 10 else FLD3 end from ...

3/ Create a view to do option 2 above automagically.

I'd tend to go for option 1 since it's likely to be the most efficient.

SQL does not specify how NULLs are sorted (although I think it specifies they must be adjacent) - that means they could be at the beginning or end (or possibly in the middle though I've never seen that happen).

The reason I bring up the efficiency aspect is that per-row functions do not scale well. As the table gets bigger, you'll find that converting NULLs into 10 every time you select will be very expensive.

It's far better to bite the bullet and just set them to 10 in the database. This will allow the DBMS to optimize queries better. And, if you ever need to use 10 for another real priority level, just change all the current 10s to 11s (or 9999s) before you start.

Answer 3

If you want records with NULL priority to appear last, I would recommend something like

ORDER BY (IFNULL(Priority, 1000000)) or something like this