quote:

---

Originally posted by tantcu

Hi guys,

I'm working on this query and wonder how could I pull off only biggest sales per corp_address_id. This is my query:
DECLARE @EndDate datetime
DECLARE @StartDate datetime
SET @EndDate = GETDATE()
SET @StartDate = DATEADD(month, -8, @EndDate)


;WITH order_history AS (

SELECT
a.corp_address_id
,a.name
,s.invoice_date
,s.invoice_no
,SUM(s.detail_price) AS sales

FROM
customer
INNER JOIN p21_view_address a ON a.id = customer.customer_id
LEFT OUTER JOIN p21_sales_history_report_view s ON s.customer_id = customer.customer_id

WHERE
s.year_for_period = 2012
AND s.parent_oe_line_uid = 0
AND (invoice_adjustment_type='C' AND s.source_type_cd = 2638 OR NOT (invoice_adjustment_type = 'C' OR invoice_adjustment_type = 'D'))
--AND [p21_sales_history_report_view].vendor_consigned = 'N' AND projected_order = 'N' AND (detail_type IS NULL OR detail_type = 0)
--AND (progress_bill_flag = 'N' OR progress_bill_flag IS NULL )


GROUP BY
a.corp_address_id
,a.name
,s.invoice_date
,s.invoice_no
)


SELECT *
FROM
(
SELECT
order_history.corp_address_id
,name
,order_history.invoice_no
,invoice_date
,sales
,ROW_NUMBER() OVER (PARTITION BY order_history.corp_address_id ORDER BY sales DESC) AS Seq
FROM order_history
)t
WHERE Seq=1
ORDER BY 1


The data table looks like this
corp_address_id name invoice_no sales
10010 Wellservice 123 $5
10010 Wellservice 124 $8
10010 Wellservice 125 $2


I want to have this query edit so that it only show the biggest sales for that corp_id ($8). If you know how, please let me know. I have tried to use MAX function but it did not give me the right answer or I might use it incorrectly.

---