Problem with 'ORDER BY' syntax

Databases
1

Hi there.

My problem today is ‘ORDER BY’ syntax.
I need order the table mytblStudents for field Name_And_Role.
Well… I try this statement:


SELECT 
   Name_And_Role
, @Daksha := locate('Daksha', Name_And_Role) x
, @ZETA := locate('ZETA', Name_And_Role) y
, CASE WHEN @Daksha > 0 THEN 0 
       WHEN @ZETA > 0 THEN 1 
  ELSE 3 END sort
     FROM `mytblStudents` 
ORDER BY sort;


Name_And_Role	x	y	sort
Daksha T - SV	1	0	0
Daksha T - SI	1	0	0
Daksha T	1	0	0
Daksha T - ES	1	0	0
Daksha T - VE	1	0	0
ZETA SAMUEL	0	1	1
ZETA LUKE	0	1	1
ZETA FERDINAND	0	1	1
ZETA PAULA	0	1	1
ZETA MARK	0	1	1
ZETA PHILIP	0	1	1
ZETA GREG	0	1	1
ZETA THOMAS	0	1	1
ZETA PETER	0	1	1
ZETA PABLO	0	1	1
ZETA LUIS	0	1	1
ZETA FABIAN	0	1	1
ZETA ALAN	0	1	1

But I need this different output. Your help would be very appreciated.


Name_And_Role		
Daksha T - SV	
Daksha T - VE	
Daksha T - ES	
Daksha T - SI	
ZETA ALAN	
ZETA FABIAN	
ZETA FERDINAND	
ZETA GREG	
ZETA LUIS	
ZETA LUKE	
ZETA MARK	
ZETA PABLO	
ZETA PAULA	
ZETA PETER
ZETA PHILIP
ZETA SAMUEL
ZETA THOMAS
Daksha T



DROP TABLE IF EXISTS `mytblStudents`;
CREATE TABLE `mytblStudents` (
  `Name_And_Role` char(50) DEFAULT NULL,
  `id` int(10) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=19 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of mytblStudents
-- ----------------------------
INSERT INTO `mytblStudents` (`Name_And_Role`, `id`) VALUES ('Daksha T', 1);
INSERT INTO `mytblStudents` (`Name_And_Role`, `id`) VALUES ('ZETA GREG', 2);
INSERT INTO `mytblStudents` (`Name_And_Role`, `id`) VALUES ('ZETA LUIS', 3);
INSERT INTO `mytblStudents` (`Name_And_Role`, `id`) VALUES ('ZETA LUKE', 4);
INSERT INTO `mytblStudents` (`Name_And_Role`, `id`) VALUES ('ZETA MARK', 5);
INSERT INTO `mytblStudents` (`Name_And_Role`, `id`) VALUES ('ZETA PETER', 6);
INSERT INTO `mytblStudents` (`Name_And_Role`, `id`) VALUES ('ZETA ALAN', 7);
INSERT INTO `mytblStudents` (`Name_And_Role`, `id`) VALUES ('ZETA FERDINAND', 8);
INSERT INTO `mytblStudents` (`Name_And_Role`, `id`) VALUES ('ZETA PHILIP', 9);
INSERT INTO `mytblStudents` (`Name_And_Role`, `id`) VALUES ('ZETA PABLO', 10);
INSERT INTO `mytblStudents` (`Name_And_Role`, `id`) VALUES ('ZETA SAMUEL', 11);
INSERT INTO `mytblStudents` (`Name_And_Role`, `id`) VALUES ('ZETA PAULA', 12);
INSERT INTO `mytblStudents` (`Name_And_Role`, `id`) VALUES ('ZETA THOMAS', 13);
INSERT INTO `mytblStudents` (`Name_And_Role`, `id`) VALUES ('ZETA FABIAN', 14);
INSERT INTO `mytblStudents` (`Name_And_Role`, `id`) VALUES ('Daksha T - SI', 15);
INSERT INTO `mytblStudents` (`Name_And_Role`, `id`) VALUES ('Daksha T - ES', 16);
INSERT INTO `mytblStudents` (`Name_And_Role`, `id`) VALUES ('Daksha T - VE', 17);
INSERT INTO `mytblStudents` (`Name_And_Role`, `id`) VALUES ('Daksha T - SV', 18);
2

And what is the logic behind this output? Why is that ‘Daksha T’ last?

3

The ‘Daksha T’ is the master students… is last in the symbolic list…
The ‘Daksha T - SV’ is the Student V ICE
The ‘Daksha T - VE’ is the V ICE Expert
The ‘Daksha T - ES’ is the Expert Solution
The ‘Daksha T - SI’ is the Solution Integrated

The ZETA series are newly accepted students…

4

If that order is fixed, then try:


SELECT 
   Name_And_Role
FROM `mytblStudents` 
ORDER BY
  CASE 
    WHEN Name_And_Role = 'Daksha T - SV' THEN 1
    WHEN Name_And_Role = 'Daksha T - VE' THEN 2
    WHEN Name_And_Role = 'Daksha T - ES' THEN 3
    WHEN Name_And_Role = 'Daksha T - SI' THEN 4
    WHEN Name_And_Role like 'ZETA%' THEN 5
    WHEN Name_And_Role = 'Daksha T' THEN 6
    ELSE 7
  END
5

thanks a lot for your help.

Your statement produce this output. The series ZETA is not in alphabetical order…

Name_And_Role
Daksha T - SV
Daksha T - VE
Daksha T - ES
Daksha T - SI
ZETA FABIAN
ZETA THOMAS
ZETA PAULA
ZETA SAMUEL
ZETA PABLO
ZETA PHILIP
ZETA FERDINAND
ZETA ALAN
ZETA PETER
ZETA MARK
ZETA LUKE
ZETA LUIS
ZETA GREG
Daksha T


SELECT
   Name_And_Role
FROM `mytblStudents`
ORDER BY
  CASE
    WHEN Name_And_Role = 'Daksha T - SV' THEN 1
    WHEN Name_And_Role = 'Daksha T - VE' THEN 2
    WHEN Name_And_Role = 'Daksha T - ES' THEN 3
    WHEN Name_And_Role = 'Daksha T - SI' THEN 4
    WHEN Name_And_Role like 'ZETA%' THEN 5
    WHEN Name_And_Role = 'Daksha T' THEN 6
    ELSE 7
  END
6

add Name_And_Role to the ORDER BY clause after the CASE expression

7

thank you very much r937.
it is right.

SELECT
   Name_And_Role
FROM `mytblStudents`
ORDER BY
  CASE
    WHEN Name_And_Role = 'Daksha T - SV' THEN 1
    WHEN Name_And_Role = 'Daksha T - VE' THEN 2
    WHEN Name_And_Role = 'Daksha T - ES' THEN 3
    WHEN Name_And_Role = 'Daksha T - SI' THEN 4
    WHEN Name_And_Role like 'ZETA%' THEN 5
    WHEN Name_And_Role = 'Daksha T' THEN 6
    ELSE 7
  END, Name_And_Role
8