homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

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)

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)

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


 8:11 am on Jun 8, 2010 (gmt 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.


Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved