SELECT MAX
-
Hi Using MS Query for Excel connecting to a SQL Server 2000 database through ODBC. I am trying to get the last purchase Order price for all products There are two tables containing the info I need: Table: poheadm (Purchase Order Header) order_no date_entered Table: podetm (Purchase Order Detail) order_no product local_expect_cost (i.e. the price) The following statement gives an error "Could not add the table "(SELECT." SELECT podetm.product, podetm.cost FROM podetm podetm, (SELECT max(poheadm.date_entered) as maxdate, poheadm.order_no FROM poheadm poheadm GROUP BY order_no) maxresults WHERE podetm.order_no = maxresults.order_no The following statement runs, but I get multiple results for each product, not just the latest price SELECT podetm.product, podetm.local_expect_cost, (SELECT max(poheadm.date_entered) FROM poheadm poheadm WHERE poheadm.order_no = podetm.order_no) FROM podetm podetm
-
Hi Using MS Query for Excel connecting to a SQL Server 2000 database through ODBC. I am trying to get the last purchase Order price for all products There are two tables containing the info I need: Table: poheadm (Purchase Order Header) order_no date_entered Table: podetm (Purchase Order Detail) order_no product local_expect_cost (i.e. the price) The following statement gives an error "Could not add the table "(SELECT." SELECT podetm.product, podetm.cost FROM podetm podetm, (SELECT max(poheadm.date_entered) as maxdate, poheadm.order_no FROM poheadm poheadm GROUP BY order_no) maxresults WHERE podetm.order_no = maxresults.order_no The following statement runs, but I get multiple results for each product, not just the latest price SELECT podetm.product, podetm.local_expect_cost, (SELECT max(poheadm.date_entered) FROM poheadm poheadm WHERE poheadm.order_no = podetm.order_no) FROM podetm podetm
Your tables are a little confusing. I'm assuming that this is a one to many relationship. That you have one header record and multiple details. Therefore an order could have multiple products on it. So what are you trying to pull back. The latest total order cost, or the latest cost of one product? Perhaps you could give us an example of what exactly you want outputted from the data.
-
Hi Using MS Query for Excel connecting to a SQL Server 2000 database through ODBC. I am trying to get the last purchase Order price for all products There are two tables containing the info I need: Table: poheadm (Purchase Order Header) order_no date_entered Table: podetm (Purchase Order Detail) order_no product local_expect_cost (i.e. the price) The following statement gives an error "Could not add the table "(SELECT." SELECT podetm.product, podetm.cost FROM podetm podetm, (SELECT max(poheadm.date_entered) as maxdate, poheadm.order_no FROM poheadm poheadm GROUP BY order_no) maxresults WHERE podetm.order_no = maxresults.order_no The following statement runs, but I get multiple results for each product, not just the latest price SELECT podetm.product, podetm.local_expect_cost, (SELECT max(poheadm.date_entered) FROM poheadm poheadm WHERE poheadm.order_no = podetm.order_no) FROM podetm podetm
Try this.
select t.product
,t.last_date
,pod.local_expect_cost
from ( select pod.order_no
,pod.product
,max(poh.date_entered) last_date
from poheadm poh
,podetm pod
where poh.order_no = pod.order_no
group by pod.order_no, pod.product ) t
,podetm pod
where pod.order_no = t.order_no
and pod.product = t.productThe temporary query could also be established as a view.
Chris Meech I am Canadian. [heard in a local bar]
-
Your tables are a little confusing. I'm assuming that this is a one to many relationship. That you have one header record and multiple details. Therefore an order could have multiple products on it. So what are you trying to pull back. The latest total order cost, or the latest cost of one product? Perhaps you could give us an example of what exactly you want outputted from the data.
HI Yes, one order can have many products. E.g. One entry in the PoHeadm table can have many line Items in Podetm table Header Table: Poheadm Fields: |date_eneterd |order_no| Data: |2007-05-28 |00001 | Data: |2007-05-29 |00002 | Detail Table: podetm Fields: |order_no |local_expec_cost |product| Data: |00001 | USD5.06 |AA01 | Data: |00001 | USD1.00 |AA02 | Data: |00001 | USD9.00 |AA03 | Data: |00002 | USD7.00 |AA01 | Data: |00002 | USD2.00 |AA04 | So above we have TWO Purchase orders. Order 00001, has 3 line items. Order two has two line items. As you can see, product AA01 is on two purchase orders, I want to create a list of ALL products, and I want the last price paid. So for the above, the data I want returned would look as follows (each product is ONLY ONCE, and has the latest price and date): |product |local_expect_cost |date_enetered| |AA01 |USD7.00 |2007-05-29 | (Data from Latest Date Order2) |AA02 |USD1.00 |2007-05-28 | (Only Ordered once) |AA03 |USD9.00 |2007-05-29 | |AA04 |USD2.00 |2007-05-29 |
-
Try this.
select t.product
,t.last_date
,pod.local_expect_cost
from ( select pod.order_no
,pod.product
,max(poh.date_entered) last_date
from poheadm poh
,podetm pod
where poh.order_no = pod.order_no
group by pod.order_no, pod.product ) t
,podetm pod
where pod.order_no = t.order_no
and pod.product = t.productThe temporary query could also be established as a view.
Chris Meech I am Canadian. [heard in a local bar]
Tks 4 reply Tried the statement you suggest, but get the following Error: Could not add the table '('.
-
Try this.
select t.product
,t.last_date
,pod.local_expect_cost
from ( select pod.order_no
,pod.product
,max(poh.date_entered) last_date
from poheadm poh
,podetm pod
where poh.order_no = pod.order_no
group by pod.order_no, pod.product ) t
,podetm pod
where pod.order_no = t.order_no
and pod.product = t.productThe temporary query could also be established as a view.
Chris Meech I am Canadian. [heard in a local bar]
Hi I also tried pulling the SQL tables into Access and running the same query, but had to change the line: max(poh.date_entered) last_date to max(poh.date_entered) AS last_date ???? But then it returns many records for each product, wheras I only want to return ONE record for EVERY product, and that record must be the once which has the latest date Maybe I explained what I'm trying to do badly, so if you get a chance, perhaps look at my reply to Kschuler's post as I try to explain what I want in more detail...
-
Hi I also tried pulling the SQL tables into Access and running the same query, but had to change the line: max(poh.date_entered) last_date to max(poh.date_entered) AS last_date ???? But then it returns many records for each product, wheras I only want to return ONE record for EVERY product, and that record must be the once which has the latest date Maybe I explained what I'm trying to do badly, so if you get a chance, perhaps look at my reply to Kschuler's post as I try to explain what I want in more detail...
My previous SQL would return too many rows. Try this instead.
select t.product
,t.last_date
,pod.local_expect_cost
from ( select pod.product
,max(poh.date_entered) last_date
from poheadm poh
,podetm pod
where poh.order_no = pod.order_no
group by pod.product ) t
,poheadm poh
,podetm pod
where poh.date_entered = t.last_date
and poh.order_no = pod.order_no
and pod.product = t.productAs long as your order header records have unique date time stamps, you should only get one record for each product. But this assumes that on the podetm table that the order_no, product fields are a unique key. If they are not a unique key, it possible that a product would appear twice. Good Luck.
Chris Meech I am Canadian. [heard in a local bar]
-
My previous SQL would return too many rows. Try this instead.
select t.product
,t.last_date
,pod.local_expect_cost
from ( select pod.product
,max(poh.date_entered) last_date
from poheadm poh
,podetm pod
where poh.order_no = pod.order_no
group by pod.product ) t
,poheadm poh
,podetm pod
where poh.date_entered = t.last_date
and poh.order_no = pod.order_no
and pod.product = t.productAs long as your order header records have unique date time stamps, you should only get one record for each product. But this assumes that on the podetm table that the order_no, product fields are a unique key. If they are not a unique key, it possible that a product would appear twice. Good Luck.
Chris Meech I am Canadian. [heard in a local bar]
Hi Chris That did not work either, I got multiple results for each product. One entry in the PoHeadm table can have many line Items in Podetm table The same product can appear many times in the podetm table, but each with a different order_no, and hence a different date_entered in the header table. I tried to write a statement in English stating what I want, hoping that I could develop that into an SQL statement, but even that proved difficult. Select each item from podetm, and return the product and cost of the order number that has the latest date_entered in the poheadm table???? Maybe if you have time to spend, you could try to write a statement based on the data below? Header Table: Poheadm Fields: |date_entered |order_no| Data: |2007-05-28 |00001 | Data: |2007-05-29 |00002 | Detail Table: podetm Fields: |order_no |local_expec_cost |product| Data: |00001 | USD5.06 |AA01 | Data: |00001 | USD1.00 |AA02 | Data: |00001 | USD9.00 |AA03 | Data: |00002 | USD7.00 |AA01 | Data: |00002 | USD2.00 |AA04 | So above we have TWO Purchase orders. Order 00001, has 3 line items. Order two has 2 line items. As you can see, product AA01 is on TWO purchase orders, I want to create a list of EACH of the products in podetm, and I want the LAST price paid based on the date_entered field in the poheadm table. So for the above, the data I want returned would look as follows (each product is ONLY ONCE, and has the latest price and date): |product |local_expect_cost |date_enetered| |AA01 |USD7.00 |2007-05-29 | (Data from Latest Date Order2) |AA02 |USD1.00 |2007-05-28 | (Only Ordered once) |AA03 |USD9.00 |2007-05-29 | |AA04 |USD2.00 |2007-05-29 |
-
HI Yes, one order can have many products. E.g. One entry in the PoHeadm table can have many line Items in Podetm table Header Table: Poheadm Fields: |date_eneterd |order_no| Data: |2007-05-28 |00001 | Data: |2007-05-29 |00002 | Detail Table: podetm Fields: |order_no |local_expec_cost |product| Data: |00001 | USD5.06 |AA01 | Data: |00001 | USD1.00 |AA02 | Data: |00001 | USD9.00 |AA03 | Data: |00002 | USD7.00 |AA01 | Data: |00002 | USD2.00 |AA04 | So above we have TWO Purchase orders. Order 00001, has 3 line items. Order two has two line items. As you can see, product AA01 is on two purchase orders, I want to create a list of ALL products, and I want the last price paid. So for the above, the data I want returned would look as follows (each product is ONLY ONCE, and has the latest price and date): |product |local_expect_cost |date_enetered| |AA01 |USD7.00 |2007-05-29 | (Data from Latest Date Order2) |AA02 |USD1.00 |2007-05-28 | (Only Ordered once) |AA03 |USD9.00 |2007-05-29 | |AA04 |USD2.00 |2007-05-29 |
I think I got it to work with this:
SELECT product, local_expect_cost, max(date_entered) FROM podetm as mainDetail, poheadm as mainHeader WHERE mainDetail.order_no=mainHeader.order_no and date_entered=(SELECT max(date_entered) FROM poheadm as subHeader, podetm as subDetail WHERE subHeader.order_no=subDetail.order_no and subDetail.product=mainDetail.product) GROUP BY product, local_expect_costAnd just to note, I think you meant to put 2007-05-28 for the date_entered in the AA03 product line of your output. I hope this works for you. -
I think I got it to work with this:
SELECT product, local_expect_cost, max(date_entered) FROM podetm as mainDetail, poheadm as mainHeader WHERE mainDetail.order_no=mainHeader.order_no and date_entered=(SELECT max(date_entered) FROM poheadm as subHeader, podetm as subDetail WHERE subHeader.order_no=subDetail.order_no and subDetail.product=mainDetail.product) GROUP BY product, local_expect_costAnd just to note, I think you meant to put 2007-05-28 for the date_entered in the AA03 product line of your output. I hope this works for you.Hi Kschuler I tried it, but it maxed out the processor, and seemed to hang. I will only be able to look at it again in the morning, but thanks for your reply in the mean time BTW, the podetm table has about 14000 records, and the poheadm table about 5000 records. Maybe thats why its slow? -- modified at 11:03 Thursday 30th August, 2007