a peek into my MIND

August 19, 2010

MySql sorting fields in a certain order.

Filed under: General — Bharat Kondeti @ 6:39 pm

MySql provides nice functionality to sort fields in certain order. This is very useful in scenarios where we have an enum Field represented as varchar and the values that it can hold are fairly static.

CHANNELS
——————————–
| ID | NAME | TYPE |
——————————–

TYPE field is a varchar that can take only SD, HD and 3D.

PROBLEM:

To get a list of channels sorted by TYPE in an order of SD, HD and 3D. This can be done in couple of different ways:


-- Using by FIELD
SELECT * FROM CHANNELS ORDER BY FIELD(TYPE, 'SD', 'HD', '3D'); 

-- Using by FIND_IN_SET
SELECT * FROM CHANNELS ORDER BY FIND_IN_SET(TYPE, 'SD,HD,3D');

If you know any other way please leave a comment

Advertisements

2 Comments »

  1. One more way would be to use ENUM(‘SD’, ‘HD’, ‘3D’).
    If you want to sort in ‘SD’, ‘HD’, ‘3D’ order, you can use ORDER BY `TYPE` ASC.

    ENUM values are sorted according to the order in which the enumeration members were listed in the column specification. (In other words, ENUM values are sorted according to their index numbers.)
    http://dev.mysql.com/doc/refman/5.0/en/enum.html

    Comment by Gintautas Miselis — August 19, 2010 @ 10:11 pm

  2. SELECT * FROM table WHERE 1 ORDER BY FIELD(fieldName, “value1”, “value2”, “value3”) ASC;

    Comment by Henrik — August 21, 2010 @ 3:41 pm


RSS feed for comments on this post.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: