Geek Answers Handbook

Is it possible to use both order and where in one request

I created a table. In one field, I have the priority of this entry (between 1-9). I did not set priority for all records, so for some records it will remain zero.

When displaying these entries on my HTML page, I simply check these priorities - if the priority exists, then I will be displayed as is, if it is zero, then I will show it as the lowest priority "10" (for display only).

The problem occurs when sorting the table. If I try to execute the sort(DESC)table, it will display 10 in the first row and then continue fine (1,2, ....).

How to solve this?

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

+5
source share
3 answers

There are several ways to solve it.

1 / Modify the data so that 10 is actually in the table (and not 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 make option 2 above automatically.

I would prefer to switch to option 1, as it is likely to be the most effective.

SQL does not indicate how NULLs are sorted (although I think it indicates that they should be contiguous) - this means that they can be at the beginning or end (or perhaps in the middle, although I never saw this happen )

, , , . , , NULL 10 .

10. . , - 10 , 10 11 9999 .

+2

, , NULL .

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)

ISNULL'ning NULL .

+4

, NULL , -

ORDER BY (IFNULL (, 1000000)) -

+1

More articles:


All Articles