UNIX for Beginners Questions & Answers

Code:

select to_char(cre_on, 'ddmm hh24'), proc_flg, count(*) from ib_prd.tb_ibmh_msg_log where message_type = 'DECHECK_OUT'
and cre_on > to_date('27/08/2016 00:00:00', 'dd/MM/yyyy hh24:mi:ss')
group by to_char(cre_on, 'ddmm hh24'), proc_flg
order by 1;

1) what does order by 1 means
2) group by is by column name, can't i use cre_on as the parameter, why need to convert to 'ddmm hh24'

The simple answer: SQL has syntax rules
#1:
order by 1 == order by the first field in the selected resultset
namely: to_char(cre_on, 'ddmm hh24') which is a character conversion that represents the internal oracle date datatype which humans cannot read easily. It is an octaword of the number of seconds elapsed since Julian day 1
#2:
You cannot group by a column that does not exist. Your resultset has what datatype? character. You want to use the oracle date datatype. Will not be accepted.

As a side note
to_date('27/08/2016 00:00:00', 'dd/MM/yyyy hh24:mi:ss')
may result in poor performance because versions of oracle before 11g may not use an index when the field referenced is defined by a function call -- to_date - in this case. If an index on that column exists of course.