SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    Dec 2004
    Location
    Sweden
    Posts
    24
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Query result sorted in defined order

    I have a table which looks like this:

    Code:
    create table `people` (
    `name` varchar(255),
    `occupation` varchar(255)
    );
    And I've inserted some rows:

    Code:
    insert into `people` (`name`,`occupation`) values ('Adam','Programmer');
    insert into `people` (`name`,`occupation`) values ('Burt','Designer');
    insert into `people` (`name`,`occupation`) values ('Cynthia','Designer');
    insert into `people` (`name`,`occupation`) values ('David','Salesman');
    insert into `people` (`name`,`occupation`) values ('Eric','Programmer');
    What I need is a query that returns the rows sorted after `occupation` like this: 'Programmer', 'Designer', 'Salesman'.
    That is, in an order defined by myself.

    Is this possible, and in that case how do I do it?

    Thanks in advance!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select ...
      from ...
    order by field(occupation
                  ,'Salesman'
                  ,'Designer'
                  ,'Programmer') desc
        , name
    the FIELD() function returns the index of the first parameter in the list

    the reason you list them in the opposite direction is in case there are occupations other than the ones in the list, because they will get a result of 0, and you want those to come last
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •