There is a mild ambiguity in the SQL language here.
The "supervisor.*" is both a parameter to the DISTINCT operation and the
set of columns to display.
Blame the creators of SQL.
On 2013-02-12 16:32, Avril wrote:
Hello,
I have 2 tables Supervisor and User_ :
CREATE TABLE supervisor (
id BIGINT NOT NULL,
userId BIGINT NOT NULL,
groupId BIGINT NOT NULL,
roleId BIGINT NOT NULL,
UNIQUE (userId, groupId, roleId),
PRIMARY KEY (id)
);
CREATE TABLE user_ (
id BIGINT NOT NULL,
userName VARCHAR(50) NOT NULL,
password VARCHAR(50),
firstName VARCHAR(50),
lastName VARCHAR(50),
UNIQUE (userName),
PRIMARY KEY (id)
);
I want to retrieve supervisors, sorted by the usernameof users.
My query is:
SELECT DISTINCT supervisor.*
FROM supervisor , user_
WHERE supervisor.userId = user_.id
ORDER BY user_.userName ASC;
When I run it, I get the following error :
[Error Code: 90068, SQL State: 90068] Order by expression
"USER_.USERNAME" must be in the result list in this case;
I don't understand why I need to retrieve the userName. This data
doesn't interest me.
In addition, this query works on MySQL.
Best regards,
Avril
--
You received this message because you are subscribed to the Google
Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
