0

Can this be done in one query?

This is my DB structure:

ID    PRICE    DATE                    CODE
1     23       2011-02-01 23:23:23     AAA
2     23       2011-02-04 22:01:01     BBB
3     25       2011-02-05 00:00:01     AAA
4     21       2011-01-09 00:00:00     BBB
5     20       2011-02-09 00:00:00     BBB

I'm trying to construct an SQL query that will produce the rows that PRICE is over 22 in ALL dates that are greater than 2011-01-30 23:59:59

So if we take the above structure as an example, it will produce one row that gives the CODE AAA as the price was above 22 in both dates that are greater than 2011-01-30 23:59:59. BBB should not match, because even though it has a price above 22 after the cut-off date, it also has a price after the cut-off date that isn't above 22.

Improve this question
0

4 Answers 4

Reset to default
2

This should work for you:

SELECT code FROM table
WHERE date > '2011-01-30 23:59:59'
GROUP BY code HAVING MIN(price) > 22

You get the codes from rows with an acceptable date and check that the minimum price (and thus all prices) is above 22 for each unique code.

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

4 Comments

This works perfectly. Another related question, is it possible to edit that query to produce any CODES that have been above 22 for X consecutive days? So it takes all the dates and checks if the CODE was above 22 for any 3 days for example..
Sorry, just checked some more results, and I see some false positives. If it makes any difference, PRICE is DECIMAL and I am trying to find PRICEs that are below 0.
@Or W, If you change the condition on the date (perhaps using BETWEEN...AND) then you should be able to do so...
@Or W, then if you want prices below 0, then you probably want to use MAX instead of MIN...
1

You could use a not exists subquery to search for rows with the same code but a lower price:

select  *
from    YourTable yt1
where   '2011-02-01' <= date
        and 22 < price
        and not exists
        (
        select  *
        from    YourTable yt2
        where   yt1.code = yt2.code
                and '2011-02-01' <= yt2.date
                and yt2.price <= 22
        )
Improve this answer

3 Comments

ran that query, MySQL server is hanging for the past 2 minutes or so
@Or W, you should check to see if you have an appropriate index for this query to use.
@a'r I'll do that, thank you. It came back and it works great... Thanks @Andomar
1 
SELECT CODE
  FROM mytable
 WHERE DATE >= '2011-01-31'
 GROUP BY CODE
HAVING MIN(PRICE) > 22
Improve this answer

4 Comments

This works much faster, but also displays incorrect results. I'm getting results with PRICE lower than 22. if it makes any difference, the PRICE column is DECIMAL
@Or W: The a.CODE = CODE was accidentally left over from an earlier version (as you pointed out, though, it doesn't affect the result). I've removed the redundant test and tested the latest version. It generates correct results, AFAICT. I should note that, in your modified sample data set, AAA and BBB both match, since ID 4 occurs before the cut-off date. I've added an extra row to your question and confirmed that my updated answer excludes BBB.
I think the problem was that you have the date wrong, too, as his post said > '2011-01-30 23:59:59', so 1/31 should be included in the tests...
@jswolf19: Thank you for catching that. I've emended the answer.
0 
select code
from table
where price > 22 and date > '2011-01-30 23:59:59'

something like that? or did you mean something else?

Improve this answer

3 Comments

I'm looking for a query that will indicate which CODES were above price 22 for a consecutive amount of time
The OP definitely meant something else. The requirement is for the match to succeed only if all prices after the cut-off date are above the threshold.
@Or W: Ok, now I understand :) I thought of new solution, but I see jswolf19 already posted it ;p

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.