Welcome to WebmasterWorld Guest from 54.145.246.183

Forum Moderators: open

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)

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



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 Jun 1, 2010 (gmt 0)

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



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

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



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
 

Featured Threads

My Threads

Hot Threads This Week

Hot Threads This Month