syntax error with GROUP BY

[jeff5656]

I'm trying to get a list of records limited to the field "lname" so that lname is unique:

 

$query = "select lname, fname, encounter.members.id 
  from encounter.members
  
  inner join inpatients on inpatients.resident = members.lname GROUP BY lname
  
  where doc_type = 'r' order by lname";

 

That gives me a syntax error.  When I remove the GROUP BY lname part, no more syntax error but then I get:

Smith

Smith

Smith

Johnson

Johnson

 

instead of

Smith

Johnson

etc.

 

 

[PFMaBiSmAd]

GROUP BY goes after the WHERE clause and  before the ORDER BY.

 

The following is the select query syntax definition/prototype (the elements, when present, must be in the order shown in the definition) -

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'

        [CHARACTER SET charset_name]

        export_options

      | INTO DUMPFILE 'file_name'

      | INTO var_name [, var_name]]

    [FOR UPDATE | LOCK IN SHARE MODE]]

 

 

[jeff5656]

Perfect. Thanks!