Ordering SQL query by specific field values

Question

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...)

None of the suggested solutions require the multiple selects/unions. They should all be much faster and easier to maintain, and the query will be MUCH easier to read. — Peter T. LaComb Jr., Dec 29 '08 at 20:15
I'm going with Peter LaComb on this one. Why are you using UNIONs? There is no need I'd go with SELECT * FROM Retail WHERE MTITLE LIKE 'somethi%' AND EDITION IN ('NE', 'OE', 'OP', '') ORDER BY CASE EDITION When 'NE' Then 1 When 'OE' Then 2 When 'OP' Then 3 Else 4 End — Pulsehead, Dec 30 '08 at 16:01
@Pulsehead you can find the answer to why I would have used unions somewhere in this very StackOverflow question about not knowing the best way to order fields. — Cyprus106, Dec 09 '15 at 18:57

score 68 · Accepted Answer · answered Dec 29 '08 at 19:49

68

Order By Case Edition
    When 'NE' Then 1
    When 'OE' Then 2
    When 'OP' Then 3
    Else 4 End

answered Dec 29 '08 at 19:49

Charles Bretana

143,358
22
150
216

OOH! More streamlined than how I do it. I'll have to remember your solution. – Pulsehead Dec 29 '08 at 19:54
Indeed - I'd not seen that syntax before. – Peter T. LaComb Jr. Dec 29 '08 at 19:56

score 7 · Answer 2 · edited Dec 04 '14 at 20:44

7

SELECT 
  /*other fields*/
  CASE WHEN 'NE' THEN 1
    WHEN "OE" THEN 2
    WHEN "OP" THEN 3
    ELSE 4
END AS OrderBy
FROM
  /*Tables*/
WHERE
  /*conditions*/
ORDER BY
  OrderBy,
  /*other fields*/

edited Dec 04 '14 at 20:44

umlcat

4,091
3
19
29

answered Dec 29 '08 at 19:47

Pulsehead

5,050
9
33
37

Thanks. Glad to know that others use this hack/kludge! makes me feel like a better programmer/dba. – Pulsehead Dec 29 '08 at 19:52
By the time I started the order by SO warned me of 3 answers. – KP. Dec 29 '08 at 19:55
Is the alias "Sequence" really a good idea? Could it not collide with reserved words on some SQL servers? There is new "CREATE SEQUENCE ..." syntax in Firebird for example. – mghie Dec 29 '08 at 21:15
I'm not personally familiar with Firebird. I use SQL Server 2005's Enterprise manager. But if Sequence doesn't work for you, then Order By can also be used. In fact, I think I'll edit the reply. – Pulsehead Dec 30 '08 at 00:43

score 5 · Answer 3 · answered Dec 29 '08 at 19:47

5

Add those values to another table with a numeric column for their rank:

Edition  Rank
NE       1
OE       2
OP       3

Join the tables, and sort on the RANK field.

answered Dec 29 '08 at 19:47

Peter T. LaComb Jr.

2,935
2
29
44

Exactly. If its not a permanent need or changes often, then the OP can use a temp table/ table variable. – StingyJack Dec 29 '08 at 19:48

score 2 · Answer 4 · answered Dec 29 '08 at 19:52

2

Try:

select *
from MyTable
order by
case [FIELD] 
    when 'NE' then 1
    when 'OE' then 2
    when 'OP' then 3
    when '' then 4
    else 5
end

answered Dec 29 '08 at 19:52

D'Arcy Rittich

167,292
40
290
283

score 1 · Answer 5 · edited Jun 15 '23 at 07:54

1

How about this?

SELECT * 
FROM RETAIL
WHERE MTITLE LIKE 'somethi%'
ORDER BY POSITION(EDITION, ' OP OE NE') DESC

If substr is empty string, the result is 1. If no match is found, the result is 0.

See the documentation of Position().

edited Jun 15 '23 at 07:54

Mark Rotteveel

100,966
191
140
197

answered Aug 17 '18 at 13:36

H.He

61
5

This is probably the most convenient (hack) approach when you just want to slap a quick query into FlameRobin to visualize a report that you want to eyeball -- it is quickly copied and edited, if say, you have just written `WHERE col IN (...)` and you want to order by the comma-separated values in IN. – mickmackusa Nov 10 '22 at 04:54

score 1 · Answer 6 · answered Jan 14 '19 at 10:20

SELECT * FROM (

SELECT 1 as Rank, R.* FROM RETAIL R WHERE MTITLE LIKE 'somethi%' AND EDITION='NE'
   UNION ALL
SELECT 2 as Rank, R.* FROM RETAIL R WHERE MTITLE LIKE 'somethi%' AND EDITION='OE'
   UNION ALL
SELECT 3 as Rank, R.* FROM RETAIL R WHERE MTITLE LIKE 'somethi%' AND EDITION='OP'
   UNION ALL (etc...)

) ORDER BY 1

score 0 · Answer 7 · edited Jan 14 '19 at 08:04

0

    CREATE TABLE #TMP
(
       ID INT IDENTITY(1,1),
       NAME VARCHAR(100),
)

INSERT INTO #TMP
SELECT 'ASHISH'
UNION ALL
SELECT 'CHANDAN'
UNION ALL
SELECT 'DILIP'
UNION ALL
SELECT 'ESHA'
UNION ALL
SELECT 'FIZA'
UNION ALL
SELECT 'MAHESH'
UNION ALL
SELECT 'VIPUL'
UNION ALL
SELECT 'ANIL'

-- I want to sort NAME column from value 'DILIP' then query will be as bellow

SELECT * FROM #TMP ORDER BY CASE WHEN NAME='DILIP' THEN '1' ELSE NAME END ASC

DROP TABLE #TMP

edited Jan 14 '19 at 08:04

Flexo

87,323
22
191
272

answered Dec 02 '15 at 09:44

Dilip Kr Singh

1,418
1
18
26

Creating tables would de-facto require exclusive access to the database, otherwise sooner or later two users would clash creating it at the same time. Also, if the network connection breaks or client application crashes or even server crashes - then this table probably remains in the database - and you do not check for this condition before trying to create it. – Arioch 'The Jan 14 '19 at 10:22

score 0 · Answer 8 · answered Dec 29 '08 at 19:52

0

Try this:

ORDER BY FIND_IN_SET(EDITION, 'NE,OE,OP,')

answered Dec 29 '08 at 19:52

user49953

41
4

FIND_IN_SET() doesn't work in SQL Server 2005. Can you tell me how you did this? I have a kludge solution, but I'm always on the lookout for a more elegant solution. – Pulsehead Dec 29 '08 at 20:02

score 0 · Answer 9 · edited Jun 06 '19 at 03:57

0

SELECT (CASE WHEN 'NE' THEN 1
    WHEN "OE" THEN 2
    WHEN "OP" THEN 3
    ELSE 4) as orden,* FROM Retail WHERE MTITLE LIKE 'somethi%' 
     AND EDITION IN ('NE', 'OE', 'OP', '') ORDER BY Orden

edited Jun 06 '19 at 03:57

Pandiyan Cool

6,381
8
51
87

answered Jun 05 '19 at 17:10

Andreu Serra Candela

1

3

Please explain what you did there. – m1k1o Jun 05 '19 at 17:19

Ordering SQL query by specific field values

9 Answers9

Linked