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

i using hsqldb-2.3.3 version, here i'm showing simple mysql query.

example:

select name,age,emailid,country players group country;

but if same query in hypersql shows error, "expression not in aggregate or group columns public.players.name"

and if apply group every column selected select statement, display results aggregation of column.

my question is, how display multiple columns 1 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

the implementation of gorup in mysql different other databases follow sql standard. in case, query shouldn't work.

the result expect contains person lowest pid each country. can write query explicitly asks this:

select name, age, emailid, country players      pid in (select min(pid) players group country)

the query first finds lowest pid each country. selects 2 rows contain these pid values.

Ben