sql query

[vascobrito]

Hello there
i have a table with 3 fields
product_id, product_num, and product_date
each product_id has several product_num, each one with a product_date.

table Products

Product_id | Product_num | Product_date
1602 1032 19990226
1602 1033 19990530
1602 1056 20000617
1605 0027 20010325
1605 0032 20020914
1607 3206 20040330

Now, my problem is that i need to get the small Product_num of each Product_id if the small Product_num has a Product_date biger then 20020101.
I was trying to use somthing like this:

"select Product_id,min(Product_num) from Products group by Product_id having Product_date >= 20020101"

This doesn't work, because it get's the small Product_num of each Product_id but without considering the Product_date.

How can i do this?
Thanks in advance

 

[PHV]

Have you tried replacing having by where ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PHV]

Something like this ?
select Product_id,min(Product_num)
from Products
where Product_date >= 20020101
group by Product_id

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[vascobrito]

yes, i've tryied that
it get's all the first Product_num of each Product_id with the Product_date bigest that 20020101, not just the Product_id and the smallest Product_num with the Product_date biger than 20020101.

 

[PHV]

Something like this ?
Select distinct Product_id,Product_num
From Products
Where Product_num=(select min(Product_num)
from Products where Product_date>=20020101)

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[vascobrito]

Right now , i can't tell you if it works, but it's taking some time. I will post as soon as i can. Hopefuly today.

Thanks PHV

 

[vascobrito]

Hi
No Luck
It didn't worked
it went on and on for 7 hours and nothing.
i finaly stoped it.
Thanks any way