Using subquery in a function

Question

well,im sort of new to mysql im stuck with the following scenario:

    SELECT 
    h.machine_id,
    h.pin_no,
    h.created_at,
    (SELECT 
            created_at
        FROM
            history AS h1
        WHERE
            h1.created_at > h.created_at
                AND h1.machine_id = h.machine_id
                AND h1.pin_no = h.pin_no
                AND h1.state = 1
        LIMIT 1) AS ended_at,

TIMEDIFF(ended_at,created_at) as elapsed

FROM
    history AS h
WHERE
    h.state = 0

i would like to use the value from subquery in a function,such as datediff, how can i achieve that? i would use cte if it was mssql :)

siride · Accepted Answer · 2015-11-11 14:30:27Z

1

You just literally embed the subquery in the function as an argument. By the way, this doesn't really have to do with subqueries, but rather with the fact that you can't reuse expressions in the same SELECT list.

SELECT 
    h.machine_id,
    h.pin_no,
    h.created_at,
    (SELECT 
            created_at
        FROM
            history AS h1
        WHERE
            h1.created_at > h.created_at
                AND h1.machine_id = h.machine_id
                AND h1.pin_no = h.pin_no
                AND h1.state = 1
        LIMIT 1) AS ended_at,
   TIMEDIFF((SELECT 
            created_at
        FROM
            history AS h1
        WHERE
            h1.created_at > h.created_at
                AND h1.machine_id = h.machine_id
                AND h1.pin_no = h.pin_no
                AND h1.state = 1
        LIMIT 1),created_at) as elapsed
FROM
    history AS h
WHERE
    h.state = 0

If you really can't stand to use the subquery twice, you can use a derived table expression to calculate the subquery which can then be used multiple times in the outer query.

SELECT
    calculations.machine_id,
    calculations.pin_no,
    calculations.created_at,
    calculations.ended_at,
    TIMEDIFF(calculations.ended_at, calculations.created_at) AS elapsed
FROM (
    SELECT 
        h.machine_id,
        h.pin_no,
        h.created_at,
        (SELECT 
                created_at
            FROM
                history AS h1
            WHERE
                h1.created_at > h.created_at
                    AND h1.machine_id = h.machine_id
                    AND h1.pin_no = h.pin_no
                    AND h1.state = 1
            LIMIT 1) AS ended_at
    FROM
        history AS h
    WHERE
        h.state = 0
) AS calculations

edited Nov 11, 2015 at 14:30

answered Nov 11, 2015 at 14:21

siride

212k5 gold badges43 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sin5k4 Over a year ago

i just dont want to use the same subquery :))

siride Over a year ago

@Sin5k4: you either have to do that, or wrap the whole thing up in a derived table expression and then you can reuse it. I'll add an edit to show what that would look like.

siride Over a year ago

@Sin5k4: it's exactly equivalent except that you can't use it for recursion and you can't reference it more than once.

Collectives™ on Stack Overflow

Using subquery in a function

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related