Order by FIELD in MYSQL

Question

I am struggling to resolve this . I have a table like this .

    +-------------+-------+
    | type        | COUNT |
    +-------------+-------+
    | A           |     1 |
    | C           |     5 |
    | B           |     4 |
    +-------------+-------+

I want to query the table and the result must be like this .

+-------------+-------+
| type        | COUNT |
+-------------+-------+
| A           |     1 |
| B           |     5 |
| C           |     9 |
| D           |     0 |
+-------------+-------+

QUERY:

select type , COUNT from TABLE order by FIELD(type,'A','B','C','D') ;

It works fine if the column type has value for 'A,B,C,D' . In some cases the order by FIELD('A','B','C','D') some columns may not have value in table . In this cases I want to put 0 for it and construct a result .

D is not there in table . So put '0' for it .

SHOW CREATE TABLE OUTPUT

CREATE TABLE `Summary` (
  `TIMESTAMP` bigint(20) NOT NULL DEFAULT '0',
  `type` varchar(50) NOT NULL DEFAULT '',
  `COUNT` bigint(19) NOT NULL,
  PRIMARY KEY (`TIMESTAMP`,`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

can you post the schema of your table? is the table with `type` and `count` a result set or the original table itself? — John Woo, Nov 16 '12 at 11:59
why don't try with set count column default value as 0 `COUNT` bigint(19) NOT NULL DEFAULT '0' — Sathish, Nov 16 '12 at 12:06

score 15 · Accepted Answer · answered Nov 16 '12 at 12:08

15

how abt this:

select a.col as type,coalesce (`COUNT`,0) as `count`
from 
(select 'A' as col union all
select 'B' as col union all
select 'C' as col union all
select 'D' as col )a
left join Table1 T
on a.col=T.type
order by FIELD(a.col,'A','B','C','D') ;

SQL fiddle demo

answered Nov 16 '12 at 12:08

Joe G Joseph

23,518
5
56
58

This answer is missing its educational explanation and makes a relatively poor candidate as a dupe target. – mickmackusa Nov 16 '22 at 07:04

Order by FIELD in MYSQL

1 Answers1

SQL fiddle demo

Linked

Related