Ordering SQL query by specific field values

sql firebird
Cyprus106 picture Cyprus106 · Dec 29, 2008 · Viewed 33.5k times · Source

I've got a sql query (using Firebird as the RDBMS) in which I need to order the results by a field, EDITION. I need to order by the contents of the field, however. i.e. "NE" goes first, "OE" goes second, "OP" goes third, and blanks go last. Unfortunately, I don't have a clue how this could be accomplished. All I've ever done is ORDER BY [FIELD] ASC/DESC and nothing else.

Any suggestions?

Edit: I really should clarify: I was just hoping to learn more here. I have it now that I just have multiple select statements defining which to show first. The query is rather large and I was really hoping to learn possibly a more effecient way of doing this: example: 

SELECT * FROM RETAIL WHERE MTITLE LIKE 'somethi%' AND EDITION='NE'
UNION 
SELECT * FROM RETAIL WHERE MTITLE LIKE 'somethi%' AND EDITION='OE'
UNION
SELECT * FROM RETAIL WHERE MTITLE LIKE 'somethi%' AND EDITION='OP'
UNION (etc...)

Answer

Charles Bretana picture Charles Bretana · Dec 29, 2008 
Order By Case Edition
    When 'NE' Then 1
    When 'OE' Then 2
    When 'OP' Then 3
    Else 4 End

Related questions

Generate insert SQL statements from a CSV file

I need to import a csv file into Firebird and I've spent a couple of hours trying out some tools and none fit my needs. The main problem is that all the tools I've been trying like EMS Data Import …

Read More
Combining UNION ALL and ORDER BY in Firebird

This is my first attempt at answering my own question, since someone may well run into this and so it might be of help. Using Firebird, I want to combine the results of two queries using UNION ALL, then sort …

Read More
C# constructing parameter query SQL - LIKE %

I am trying to build SQL for a parameter query in C# for a query which will contain the LIKE %% command. Here is what I am trying to acheive (please note that the database is Firebird) var SQL = string.format("…

Read More