MySQL Forums
Forum List  »  Optimizer & Parser

Re: MySQL subquery
Posted by: Øystein Grøvlen
Date: October 28, 2016 01:03AM

Hi,

According to the SQL standard, the GROUP BY clause can not refer to aliases from the SELECT list. This is because GROUP BY is locically processed before SELECT. See http://stackoverflow.com/questions/3841295/sql-using-alias-in-group-by for a discussion on this.

MySQL (and some other database systems) allows referring to SELECT aliases in the GROUP BY clause. However, original column names will take precedence over aliases. Hence, in your first query, GROUP BY refers to the original tsmark column of the derived table t. So the work around in your case is to either use another name for the alias, or to move the date formatting into the subquery.

Regards,

Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway

Options: ReplyQuote


Subject
Views
Written By
Posted
1184
October 27, 2016 02:51PM
Re: MySQL subquery
745
October 28, 2016 01:03AM
736
November 06, 2016 08:50PM
743
November 07, 2016 01:39AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.