Hello All,

Seems this forum is full of knowledgable people. Here's what I'd like to know how to do:

I would like to write a SELECT statement that contains an ORDER BY clause on a particular column
where the ordering would be according to a predetermined order that is not necessarily alphabetical or numeric or
a combination of both.

So for example if the column in question for the ORDER BY clause contains several records with
the string 'CAR' and others with the string 'DOG' and others with the string 'MOUSE' I would like to be
able to explicitely states that the DOG records should be ordered first, then CAR records, then MOUSE records,
then whatever is left over that is not any of the above. On a different SELECT of the same data I'd perhaps like MOUSE to come first, etc.

Is there an easy way to do this ?

Thanks

Oliver

oliver wrote:

> Hello All,
>
> Seems this forum is full of knowledgable people. Here's what I'd like
> to know how to do:
>
> I would like to write a SELECT statement that contains an ORDER BY
> clause on a particular column where the ordering would be according
> to a predetermined order that is not necessarily alphabetical or
> numeric or a combination of both.
>
> So for example if the column in question for the ORDER BY clause
> contains several records with the string 'CAR' and others with the
> string 'DOG' and others with the string 'MOUSE' I would like to be
> able to explicitely states that the DOG records should be ordered
> first, then CAR records, then MOUSE records, then whatever is left
> over that is not any of the above. On a different SELECT of the same
> data I'd perhaps like MOUSE to come first, etc.
>
> Is there an easy way to do this ?
>
> Thanks
>
> Oliver

Kia Orana Oliver

This is an example using v3.30 syntax ... probably with errors

SELECT IF(Field1 = 'DOG' THEN '01'
      ELSE IF(Field1 = 'CAT' THEN '02'
     ELSE IF(Field1 = 'MOUSE' THEN '03'
      ELSE '99'))) AS Sequence.
     OtherField
FROM MyTable
ORDER BY Sequence, ...

v4 probably does this prettily with a CASE statement or whatever.

Cheers

Jeff


--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz
+
Joan and Jeff Cook
The Cooks Oasis
www.cookislandsoasis.com

Jeff

If the strings are unique and guaranteed how about

SELECT POS(Field1,'DOG;CAT;'MOUSE) AS Sequence, etc FROM MyTable ORDER BY Sequence,

As a bonus should give the unknowns at the top

Roy Lambert [Team Elevate]

Roy

> SELECT POS(Field1,'DOG;CAT;'MOUSE) AS Sequence, etc FROM MyTable ORDER BY
Sequence,

Nearly works for options as given but not so good with MEERCAT or DORMOUSE
added in front <bg>

John

John


easy


SELECT POS(':' + Field1 + ':' , ';DOG;MEERCAT;CAT;'MOUSEAS Sequence, etc FROM MyTable ORDER BY Sequence


Roy Lambert

Roy
> easy
>
>
> SELECT POS(':' + Field1 + ':' , ';DOG;MEERCAT;CAT;'MOUSEAS Sequence,
etc FROM MyTable ORDER BY Sequence

Yes - if you want an 11949 or all not found <vbg>

John

Oliver,

SELECT
  YourColumn1,
  ...
  YourColumnN,
  CASE
    WHEN POSITION('DOG', UPPER(TheColumn)) > 0 THEN 1
    WHEN POSITION('CAR', UPPER(TheColumn)) > 0 THEN 2
    WHEN POSITION('MOUSE', UPPER(TheColumn)) > 0 THEN 3
  ELSE
    0
  END AS OrderNumber
FROM
  YourTable
ORDER BY
  OrderNumber


--
Fernando Dias
[Team Elevate]

John


Good grief some people need their hands holding

select POS('*'+_surname+'*','*Smith*Jones*Watling*') as Seq,_surname from contacts order by seq

The problem with the other one was that I used a semicolon as a separator and the parser has a small fit <vbg>

Roy Lambert [Team Elevate]

Roy

> Good grief some people need their hands holding

LOL

I didn't even notice the semi colons.  It was the misplaced quote to which I
was referring.

John

John


Fair cop guv - I never even noticed the misplaced quote

Roy Lambert