SQL Anywhere error - Function or column reference to 'id' must also appear in a GROUP BY

Question

I have 2 tables:

test table:

Column                           Type                             Nullable Primary Key 
-------------------------------- -------------------------------- -------- ----------- 
id                               integer                                 0           1 
name                             varchar(255)                            1           0 
price                            varchar(255)                            1           0

rows:

id name price
1  BMW  3000
2  AUDI  2500

test_2 table:

Column                           Type                             Nullable Primary Key 
-------------------------------- -------------------------------- -------- ----------- 
id                               integer                                 0           1 
ad_id                            integer                                 1           0 
user_id                          integer                                 1           0 
price                            integer                                 1           0 

rows:

         id       ad_id     user_id       price 
----------- ----------- ----------- ----------- 
          1           1          23        2000 
          2           1          23        2200 
          3           1          22        2050 

I need to get this result:

BMW 23 2200 
BMW 22 2050

My SQL:

SELECT * FROM test, test_2 WHERE test_2.ad_id=test.id GROUP BY user_id ORDER BY test_2.price DESC

but always I get this error:

Could not execute statement.
Function or column reference to 'id' must also appear in a GROUP BY
SQLCODE=-149, ODBC 3 State="42000"
Line 1, column 1

I use SQL Anywhere. Thanks for any help.

Answer 1

All columns in group by should be in select clause apart from aggregate functions

SELECT test.name, test_2.user_id, max(test_2.price)
FROM test, test_2 
WHERE test_2.ad_id=test.id 
GROUP BY test.name, test_2.user_id
ORDER BY test_2.price DESC