Welcome to WebmasterWorld Guest from 54.162.3.15

Forum Moderators: open

Message Too Old, No Replies

# A sequencing trick I stumbled on

## When your sequence field defaults to zero, and you want nonzero's first

#### rocknbil

12:53 am on May 29, 2010 (gmt 0)

#### Senior Member

joined:Nov 28, 2004
posts:7999

Stumbled across something you may find useful, I'd never really thought about it until today.

What most probably already know, an easy method of allowing user-selectable ordering of items is with an integer int/tinyint field:

sequence tinyint(3) default 0

The default behavior will always put zero's first. A customer today thought it would be better if only the numbers he selected for his items were considered, which is "kind of" against the natural behavior of order by. "I set it to 1 and it should be number 1" (even if all the other item sequences are zero.) In these instances you can do this, which will put the zero's below.

A full test:

create table test2 (id int(11) primary key auto_increment, title varchar (255), sequence tinyint(3) default 0);

insert into test2 (title,sequence) values ('a row1',0);
insert into test2 (title,sequence) values ('d row2',0);
insert into test2 (title,sequence) values ('w row3',0);
insert into test2 (title,sequence) values ('h row4',0);
insert into test2 (title,sequence) values ('r row5',0);
insert into test2 (title,sequence) values ('k row6',5);
insert into test2 (title,sequence) values ('m row7',9);
insert into test2 (title,sequence) values ('a row8',18);
insert into test2 (title,sequence) values ('a row8',1);

select * from test2 order by sequence>0 desc, sequence asc, title asc;

You'll get 1,5,9,18 at the top followed by the zero's, with those ordered alphabetically.

Might be useful if you ever come across this idea. I just hope I can find this thread if I ever need it again . . . lol . . .

Edit: The original example had 1,2,3 in the last rows, in that order, so thought at first it was ordering by default and not actually "working." I adjusted the above example to demonstrate that's not the case.

#### whoisgregg

9:29 pm on June 1, 2010 (gmt 0)

#### Senior Member from US

joined:Dec 9, 2003
posts:3416

Thanks for posting that rocknbil, expressions in the ORDER BY clause are a great tool! :)

#### dreamcatcher

8:11 am on June 8, 2010 (gmt 0)

#### Senior Member

joined:Mar 30, 2003
posts:3719

Thanks rocknbil. :)

If you`ve only got a few set values, you can also order using the FIELD operator.

order by FIELD(sequence,1,5,9,18,0)

Can be useful if you know the values.

dc