Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Order by FIELD in MYSQL

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
like image 283
kannanrbk Avatar asked Nov 16 '12 11:11

kannanrbk


People also ask

What is MySQL ORDER BY?

The MySQL ORDER BY Keyword The ORDER BY keyword is used to sort the result-set in ascending or descending order. The ORDER BY keyword sorts the records in ascending order by default.

How do I sort by field in SQL?

The ORDER BY statement in SQL is used to sort the fetched data in either ascending or descending according to one or more columns. By default ORDER BY sorts the data in ascending order. We can use the keyword DESC to sort the data in descending order and the keyword ASC to sort in ascending order.

Can we use 2 ORDER BY in MySQL?

If you want to select records from a table but would like to see them sorted according to two columns, you can do so with ORDER BY . This clause comes at the end of your SQL query.

What is GROUP BY and ORDER BY in MySQL?

Group by statement is used to group the rows that have the same value. Whereas Order by statement sort the result-set either in ascending or in descending order.


1 Answers

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

like image 186
Joe G Joseph Avatar answered Nov 03 '22 03:11

Joe G Joseph