How can I rewrite this query by not having AS?

Question

I'm completing a HackerRank challenge, but the documentation says I should not use the AS keyword:

I need to rewrite this query in MySQL so it doesn't include the AS in WITH A AS, nor AS in SELECT...AS test

WITH A AS (
    SELECT DISTINCT
        MAX( LENGTH( customer_id ) ) AS test
    FROM
        orders

    UNION

    SELECT DISTINCT
        MIN( LENGTH( customer_id ) )
    FROM
        orders
)
SELECT
    test,
    LENGTH(test)
FROM
    A

https://stackoverflow.com/questions/19567732/mysql-as-keyword — JoSSte, Nov 21 '22 at 19:11
Using `SELECT DISTINCT` with `MAX(...)` or `MIN()` without a `GROUP BY` is redundant because those inner-queries will only return 1 row. — Dai, Nov 21 '22 at 19:14
Ty JoSSte. Dai, I'm doing problems on hackerrank and they ask you don't include AS in queries as it will give bad results. — Klein, Nov 21 '22 at 19:14
@Klein Please share a link or screenshot of the message from HackerRank - I'm skeptical that they'd say that... — Dai, Nov 21 '22 at 19:27
@Klein Wow, that's _really janky_. You should contact them about that... — Dai, Nov 21 '22 at 19:33
It's true that _in Oracle SQL_ they disallow `AS` for defining a table alias for no good reason — they just never got around to implementing that syntax _in 40 years._ But it's standard SQL syntax, and in other implementations it is allowed. This sounds like hackerrank are ignorant of standard SQL. — Bill Karwin, Nov 21 '22 at 20:05

score 1 · Accepted Answer · answered Nov 21 '22 at 19:15

1

The WITH clause is using for declare a VIEW, so you can rewrite it like below


SELECT
    test,
    LENGTH(test)
FROM
    (
 SELECT DISTINCT
        MAX( LENGTH( customer_id ) ) AS test
    FROM
        orders

    UNION

    SELECT DISTINCT
        MIN( LENGTH( customer_id ) )
    FROM
        orders)

answered Nov 21 '22 at 19:15

viking

260
2
10

2

"The `WITH` clause is using for declare a `VIEW`" <-- That's nonsense. `WITH` is for CTEs and has nothing special to do with `VIEW` objects. – Dai Nov 21 '22 at 19:15
ofc this is not VIEW object.. I mean view as queries reusable or named query – viking Nov 21 '22 at 19:18
The correct term for that is a [CTE](https://dev.mysql.com/doc/refman/8.0/en/with.html), not a "reusable query" or "named query". – Dai Nov 21 '22 at 19:19
Yes, the correct term is CTE but it is used for e.g. better code readability, so yes for reusable query – viking Nov 21 '22 at 19:26
The ANSI/ISO SQL standard term is actually `WITH`, (optional) Feature T121. – jarlh Nov 21 '22 at 19:34
@jarlh My copy of the SQL-1999 spec refers to them as `COMMON_TABLE` expressions (part 2, page 253). – Dai Nov 21 '22 at 19:39
@Dai, interesting, I didn't know that. However, COMMON_TABLE can't be be found in SQL-2003, SQL-2008, SQL-2011 or SQL-2016. No idea why it was changed. – jarlh Nov 21 '22 at 19:45
@jarlh Looking at the SQL-2011 spec now, the term for `COMMON_TABLE` now [seems to be "`table subquery`"](https://imgur.com/a/cD5FcH5) - I'm hesistant to refer to the subquery itself as a "`WITH` query" though. – Dai Nov 21 '22 at 19:55

How can I rewrite this query by not having AS?

1 Answers1