December 10, 2008 at 8:24 am
I want to get all the columns from the record with the most recent order date (out of n records). I want to return location, item_id, item_desc, product_group.
Then I need to have calcualate the sum of quantity ordered and extended price from n records.
Any clues on how I can get this?
December 10, 2008 at 9:07 am
If you don't get an answer to this, it's likely because what you're asking for is somewhat obscure. Please refer to the link in my signature for how to provide some table structure / sample data / expected results for the query your'e trying to write.
December 10, 2008 at 9:24 am
ORDER BY gets you ordering.
SUM() gets you a sum.
the WHERE clause can filter things for you.
We'd be happy to help, but we'd like to see more info, as mentioned above, and an attempt from you.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 10, 2008 at 12:36 pm
SELECT O.*
FROM dbo.Order O
INNER JOIN (
select MAX(OrderDate) as MaxDate
from dbo.Order ) LO ON O.OrderDate= LO.MaxDate
_____________
Code for TallyGenerator
December 11, 2008 at 8:50 pm
sorry guys, I was in too much of a hurry.
Basically, Iwant to select the row with the latest order date for n rows with the same location_id, product_group_id and item_id. Then I want to sum order_qty and extended price for the n rows and return all the data in the row with latest order date and the sum of all 3 rows order_qty and Extened_price.
I tried this
SELECT location_id,
product_group_id,
item_id,
max(hdr_order_date),
max(item_description),pricing_unit,
Current_Cost,supplier_id,
supplier_name,
supplier_part_noFROM purchasing_report_view
GROUP BY location_id,
product_group_id,
item_id,
supplier_id,
pricing_unit,
Current_Cost,
supplier_name,
supplier_part_no
HAVING (((location_id)=101)) AND ((product_group_id)='brooms')
ORDER BY product_group_id, item_id
with no success.
Can you help?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply