CODEWITHSUNDEEP
mysqlwhere-clause
0xC0DEGURU
0xC0DEGURU

Reputation: 1792

How to avoid statement duplication in where clause in MySQL?

Is there any way to use an alias as a substitute of a single result query in WHERE clause to avoid statement duplication in the following query?

SELECT * 
FROM (
    SELECT *
    FROM SomeTable T 
    WHERE created <= (CASE WHEN 
        (SELECT created 
        FROM SomeTable 
        WHERE sales > 0 
        ORDER BY created DESC 
        LIMIT 1)
    IS NULL THEN NOW() ELSE 
    (SELECT created 
        FROM SomeTable 
        WHERE sales > 0 
        ORDER BY created DESC 
        LIMIT 1)
    END)
    ORDER BY created DESC LIMIT 10
) as inverseOrder
ORDER BY created ASC;

Is it possible to modify that query to avoid the statement duplication. I.e. something like that?

SELECT * 
FROM (
    SELECT *
    FROM SomeTable T 
    WHERE created <= someAliasssssssss
    IS NULL THEN NOW() ELSE someAliasssssssss
    ORDER BY created DESC LIMIT 10
) as inverseOrder
ORDER BY created ASC;

Upvotes: 0

Views: 41

Answers (1)

forpas
forpas

Reputation: 164099

You can use COALESCE():

SELECT * 
FROM (
    SELECT *
    FROM SomeTable T 
    WHERE created <= COALESCE(
        (SELECT created 
        FROM SomeTable 
        WHERE sales > 0 
        ORDER BY created DESC 
        LIMIT 1), NOW())
    ORDER BY created DESC LIMIT 10
) as inverseOrder
ORDER BY created ASC;

Upvotes: 1

Related Questions