How to select multiple columns but group by only one column in Hypersql database(HSQLDB)?

I am using HSQLDB-2.3.3 version, here I'm showing a simple MySQL query.

Example:

SELECT name,age,emailid,country FROM players GROUP BY country;

But if I do same query in HyperSQL it shows an error, "Expression not in aggregate or group by columns PUBLIC.PLAYERS.NAME"

And if I apply group to every column that I selected with select statement, it display results with aggregation of all column.

My question is, how to display multiple columns with one column aggregate (GROUP BY) in HyperSQL ??

Table Players:

pid name emailid country region age

1 Samual Samual@gmail.com INDIA DELHI 25

2 Vino Vino@gmail.com INDIA DELHI 20

3 John John@gmail.com INDIA DELHI 20

4 Andy Andy@gmail.com INDIA DELHI 22

5 Brian Brian@hotmail.com America DELHI 21

6 Dew Dew@hotmail.com America DELHI 24

7 Kris Kris@hotmail.com America DELHI 25

8 William William@hotmail.com INDIA DELHI 26

9 George George@hotmail.com INDIA DELHI 23

10 Peter Peter@gmail.com INDIA DELHI 19

11 Tom Tom@gmail.com America DELHI 20

12 Andre Andre@hotmail.com INDIA DELHI 20

Expected Result:

name age emailid country

Brian 21 Brian@hotmail.com America

Samual 25 Samual@gmail.com INDIA

Solution

The implementation of GORUP BY in MySQL is different from other databases that follow the SQL Standard. In this case, the query shouldn't work.

The result that you expect contains the person with the lowest pid for each country. You can write a query that explicitly asks for this:

SELECT name, age, emailid, country FROM players 
    WHERE pid IN (SELECT MIN(pid) FROM players GROUP BY country)

The query first finds the lowest pid for each country. It then selects the two rows that contain these pid values.