Welcome to WebmasterWorld Guest from

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

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

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
votes: 0

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.
9:29 pm on June 1, 2010 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member whoisgregg is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Dec 9, 2003
votes: 0

Thanks for posting that rocknbil, expressions in the ORDER BY clause are a great tool! :)
8:11 am on June 8, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member dreamcatcher is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Mar 30, 2003
votes: 0

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.