Using Dynamic SQL in MySQL (Sub-Query with Result of Query)

Question

I'm not sure this is even possible but I know if it is, the stackoverflow community is where I'll find the answer. I'm basically trying to pre-process some data so that I can speed up my application. Here is an example of what I'm trying to do:

SELECT id 
FROM data AS data1 
WHERE rangeVal < (SELECT rangeVal FROM data WHERE id = data1.id - 1)
AND actiondate >= '1980-05-18' 
AND actiondate <= '2012-05-18'
ORDER BY actiondate DESC

Essentially, I'm trying to get the IDs of my data only if their rangeVal is less than the rangeVal from the next day.

Any help or points in the right direction would be greatly appreciated!!

Your query seems correct; you could use BETWEEN '1980-05-18' AND '2012-05-18', but engine should be good enough to do it for you. Did you test your query? What's wrong? — Marco
– Marco, Commented Jun 19, 2012 at 12:32
What's the result you get with this query (which seems correct at first sight) ? — BMN
– BMN, Commented Jun 19, 2012 at 12:33
Just to clarify why I removed the Dynamic-SQL tag... Dynamic SQL is where a SQL command is built up dynamically. Such as introducing new JOINs or WHERE clauses. The example query in the question just uses a correlated sub-query (the result of the sub-query is correlated to the outer query). — MatBailie
– MatBailie, Commented Jun 19, 2012 at 12:45

Michael Berkowski · Accepted Answer · 2012-06-19 12:41:54Z

1

If your days are incremental and you have a row for each day, rather than try to rely on subtracting from an incremental id, which can be highly unreliable due to row deletions and other issues, you can join against the table using the DATE_SUB() or DATE_ADD() as the join condition:

SELECT 
  id
FROM 
  data data_today
  /* JOIN on a date manipulation */
  JOIN data data_tomorrow ON DATE_ADD(data_today.actiondate, INTERVAL 1 DAY) = data_tomorrow.action_date
WHERE 
  data_today.rangeVal < data_tomorrow.rangeval
  AND data_today.actiondate BETWEEN '1980-05-18' AND '2012-05-18'

edited Jun 19, 2012 at 12:41

answered Jun 19, 2012 at 12:34

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

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

Comments

will · Accepted Answer · 2012-06-19 12:40:31Z

0

The sub query is also fine

SELECT id 
FROM data AS data1 
WHERE rangeVal < 
    (
     SELECT rangeVal FROM data d2 WHERE d2.id = data1.id  AND d2.actiondate= 
     DATE_ADD(d2.action_date, INTERVAL 1 DAY)
    )
AND actiondate BETWEEN '1980-05-18' AND '2012-05-18'
ORDER BY actiondate DESC

answered Jun 19, 2012 at 12:40

will

111 bronze badge

Collectives™ on Stack Overflow

Using Dynamic SQL in MySQL (Sub-Query with Result of Query)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related