Order by 1

[dbadmin]

Hi,

I have a query which has an IN clause like below

select tab.col1, tab.col2
where tab.col1 in ('val1', 'val6', 'val3');

If I use the regular order by statement, the output gets ordered alphabetically. Is there any way I could have the output ordered by the same way inside the IN clause, like below

val1 test1
val6 test6
val3 test3
.............
.............

Any help is really appreciated.

Thanks,
dbadmin

 

[PHV]

Perhaps this clause ?
ORDER BY IIf(col1='val1',1,IIf(col1='val6',2,3))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mp9]

An alternative: untested but worth a try maybe...

ORDER BY (CInt(Right(Col1,1)) Mod 4)

http://www22.brinkster.com/accessory/

 

[dhookom]

dbadmin,
Try:

select tab.col1, tab.col2
where tab.col1 in ('val1', 'val6', 'val3')
ORDER BY Instr("val1,val6,val3",[col1]);

PH,
Congrats on Tipmaster for the week!

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[dbadmin]

Hi Duane,

That worked like a charm.

Thanks you
dbadmin