mysql "with" clause

Question

I'm trying to use mysql to create a view with the "with" clause

with authorRating(aname, rating) as
   select aname, avg(quantity)
   from book
   group by aname

but it doesn't seem like mysql supports this.

I thought this was pretty standard and I'm sure Oracle supports this. Is there anyway to force mysql to use the "with" clause? I've tried it with the MyISAM and innoDB engine. Both of these don't work.

Answer 1

have you had a look in the manual at www.mysql.com

Answer 2

Subselects as values usually must be in parentheses.

Update: not sure what syntax you are trying to use. Could you show a more full statement? CREATE VIEW takes a SELECT statement which, afaik, may have a WITH but only where it would be valid in an independent SELECT.

Update 2: err, I was thinking of CASE, not WITH. Googling doesn't show any special WITH syntax for Oracle except the WITH CHECK OPTION that mysql also supports. Please start by describing what are you trying to achieve?

Update 3: just at a guess, you mean something like this:

select ... from foo, (select aname avg(quantity) as rating from book group by aname) as authorRating where ...

Answer 3

Oracle does support WITH.

It would look like this.

WITH emps as (SELECT * FROM Employees)
SELECT * FROM emps WHERE ID < 20
UNION ALL
SELECT * FROM emps where Sex = 'F'

@ysth WITH is hard to google because it's a common word typically excluded from searches.

You'd want to look at the SELECT docs to see how subquery factoring works.

I know this doesn't answer the OP but I'm cleaning up any confusion ysth may have started.

Answer 4

MySQL does not support queries using the WITH syntax defined in SQL-99.

As far as I know, only Oracle, IBM DB2, and Microsoft SQL Server have implemented this syntax.

Answer 5

You've got the syntax right:

WITH AuthorRating(AuthorName, AuthorRating) AS
   SELECT aname         AS AuthorName,
          AVG(quantity) AS AuthorRating
   FROM Book
   GROUP By Book.aname

However, as others have mentioned, MySQL does not support this command. WITH was added in SQL:1999; the newest version of the SQL standard is SQL:2008. You can find some more information about databases that support SQL:1999's various features on Wikipedia.

MySQL has traditionally lagged a bit in support for the SQL standard, whereas commercial databases like Oracle, SQL Server (recently), and DB2 have followed them a bit more closely. PostgreSQL is typically pretty standards compliant as well.

You may want to look at MySQL's roadmap; I'm not completely sure when this feature might be supported, but it's great for creating readable roll-up queries.