Sorting a recordset

Databases
1

Hi

I have the following query

SELECT [eventid], [name],[date] FROM events WHERE eventid IN(22,13,18)

I’d like to ensure the records are returned sorted by the eventid in the order the eventid’s appear in the IN statement.

EG:

22 | Event ABC | 28/09/2012
13 | Event XYZ | 12/08/2012
18 | Event CDE | 22/09/2012

Is this possible?

2

very possible

ORDER
    BY CASE WHEN eventid = 22 THEN 'curly
            WHEN eventid = 13 THEN 'larry'
                              ELSE 'moe' 
        END
3

Thanks Rudy, and I should have probably made this clear in my original post, is that valid for Access SQL or is some bizarre syntax required?

4

Scratch that I think I have it!


SELECT eventid,[name],[date] FROM events WHERE eventid IN(22,18,13) ORDER BY IIF(eventid = 22,'curly',IIF(eventid = 13, 'larry','moe'))
5

ace! (you got it in one) :slight_smile:

p.s. if you add a 4th event id, you can use ‘shemp’ – but after that, curly joe and joe would be out of sequence

6

Thanks, fortunately I only need to go to 3.