Is there any way for an explicit sort order? Ex:

Select ID, Name, Val from TblMyTable order by Name ("john", "andrew", "bill");

|  ID  |  NAME  | VAL
--------------------------------
  3   |  John     | 'abc'
  1   |  Andrew | '321'
  2   | Bill        | NULL

On 2/02/2016 9:06 a.m., Gregory T Swain wrote:
> Is there any way for an explicit sort order? Ex:
>
> Select ID, Name, Val from TblMyTable order by Name ("john", "andrew", "bill");
>
> |  ID  |  NAME  | VAL
> --------------------------------
>     3   |  John     | 'abc'
>     1   |  Andrew | '321'
>     2   | Bill        | NULL
>

How about:-

SELECT ID,
       Name,
       Val,
       IF(Name = 'john'        THEN 1
       ELSE IF(Name = 'andrew' THEN 2
       ELSE IF(Name = 'bill  ' THEN 3
       ELSE 0))) AS Seq

-- probably a CASE statement instead of the IF's

FROM TblMyTable
ORDER BY seq, ID

HTH

Jeff

Gregory


If you only have a few names/records to bother about I'd use Jeff's approach. If there are more I'd add in a computed column and come up with a mechanism to generate a value based on the name - like soundex but customised to your needs and then sort of the computed column.

Roy Lambert