SQL Query , get data from table by 2 fields

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.

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.

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
        )

SELECT CODE
  FROM mytable
 WHERE DATE >= '2011-01-31'
 GROUP BY CODE
HAVING MIN(PRICE) > 22

select code
from table
where price > 22 and date > '2011-01-30 23:59:59'

something like that? or did you mean something else?