Re: simple SQL query question

On 7 Dec 2005 10:52:00 -0800, "jimi_xyz_at_hotmail.com" <jimi_xyz_at_hotmail.com> wrote:

>Hi,
>Ok I have this simple query..
>
>SELECT id, name
>FROM people
>where id in (176, 181, 22, 61);
>
>which returns..
>
>id name
>---------------------------------------------------------------------
>22 John
>61 Sam
>176 Tom
>181 Pam
>
>What I want is for the query to return the "names" and "ids" in the
>order of the in() clause.
>Since my in() clause looks like this, in (176, 181, 22, 61). I want my
>results to look like this..
>
>id name
>---------------------------------------------------------------------
>176 Tom
>181 Pam
>22 John
>61 Sam
>
>As you can see they are in the order of the IN() clause, (176, 181, 22,
>61). I am not to sure on how I could do this, one of my friends told me
>to use decode, so i have been messing around with that all day today.
>Any help or ideas will be appreciated.
>
>Thanks,
>-Jimmie

column ranking noprint

SELECT decode(id, 176,1, 181, 2, 22, 61, 3, NULL) ranking, id, name FROM people
where id in (176, 181, 22, 61)
order by decode(id, 176,1, 181, 2, 22, 61, 3, NULL) nulls last;

assuming 8i or higher.

--
Sybrand Bakker, Senior Oracle DBA