Welcome to WebmasterWorld Guest from 54.160.221.82

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
posts:7999
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

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

joined:Dec 9, 2003
posts:3416
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
posts:3719
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.

dc
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members