CODEWITHSUNDEEP
mysqlsql
user_g
user_g

Reputation: 35

sql GROUP BY produces syntax error

I can't understand what is wrong with this query. It keeps producing a syntax error. Thank you very much.

SELECT 
    `employees`.`ID`, `employees`.`LNAME`, `employees`.`FNAME`, `employees`.`MINIT`, 
    `leave_app`.`ID`, `leave_app`.`CONTROL_NUM`, `leave_app`.`DATE_FILED`,
    `leave_app`.`DATE_FROM`, `leave_app`.`DATE_TO`, `leave_app`.`TYPE`, 
    `leave_app`.`EMP_FK`
FROM employees
LEFT JOIN `trackr_pgso`.`leave_app` ON `employees`.`ID` = `leave_app`.`EMP_FK`
ORDER BY `employees`.`ID` ASC
GROUP BY `employees`.`ID`
LIMIT 0 , 30

I have something like this:

1|  6/7/2014
1|  6/30/2014
1|  7/1/2014
2|  6/15/2014
3|  6/29/2014

What i want is like this:

1|  6/7/2014
    6/30/2014
    7/1/2014
2|  6/15/2014
3|  6/29/2014

Upvotes: 0

Views: 3252

Answers (1)

Jens
Jens

Reputation: 69460

You have to put your order by clause after your group by clause:

SELECT `employees`.`ID` , `employees`.`LNAME` , `employees`.`FNAME` , `employees`.`MINIT` , `leave_app`.`ID` , `leave_app`.`CONTROL_NUM` , `leave_app`.`DATE_FILED` , `leave_app`.`DATE_FROM` , `leave_app`.`DATE_TO` , `leave_app`.`TYPE` , `leave_app`.`EMP_FK`
FROM employees
LEFT JOIN `trackr_pgso`.`leave_app` ON `employees`.`ID` = `leave_app`.`EMP_FK`
GROUP BY `employees`.`ID`
ORDER BY `employees`.`ID` ASC
LIMIT 0 , 30

You have to group first and than you have to order .

Here is the syntax from Mysql documentation:

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE 'file_name' export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR UPDATE | LOCK IN SHARE MODE]]

Upvotes: 4

Related Questions