CODEWITHSUNDEEP
sqlmysql
Or Weinberger
Or Weinberger

Reputation: 7472

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.

Upvotes: 0

Views: 447

Answers (4)

Marcelo Cantos
Marcelo Cantos

Reputation: 185862

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

Upvotes: 1

jswolf19
jswolf19

Reputation: 2303

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.

Upvotes: 2

rsc
rsc

Reputation: 4434

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

something like that? or did you mean something else?

Upvotes: 0

Andomar
Andomar

Reputation: 238086

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
        )

Upvotes: 1

Related Questions