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

Question

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 [email protected] INDIA DELHI 25

2 Vino [email protected] INDIA DELHI 20

3 John [email protected] INDIA DELHI 20

4 Andy [email protected] INDIA DELHI 22

5 Brian [email protected] America DELHI 21

6 Dew [email protected] America DELHI 24

7 Kris [email protected] America DELHI 25

8 William [email protected] INDIA DELHI 26

9 George [email protected] INDIA DELHI 23

10 Peter [email protected] INDIA DELHI 19

11 Tom [email protected] America DELHI 20

12 Andre [email protected] INDIA DELHI 20

Expected Result:

name age emailid country

Brian 21 [email protected] America

Samual 25 [email protected] INDIA

Answer 1

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.