sql - oracle - selecting the first and last record for specific group/sku -


I'm trying to answer but no success.

Obtain: The price for the previous purchase and the price of the group and grouped by SKU.

The query result may be:

  sku first_purchase_price Last_purchase_price BC123 3.0 9 6.68 QERT1 9.09 13.23  My query   
  SELECT sku, PRICE, MAX (purchase_DATE), FROM SQL error from MIN (purch_D_DAY): ORA 00979: Group No by Expression 00 9 79. 00000:   - "Expression Based No group on " 

or

  SQL error: ORA-00937: Not a group group function 00937. 00000 - "Any single group group function"  

Any help is greatly appreciated.

  SKU TRANSAC_ID purchase_DATE PRICE ----- - ------------------------------ ---------- BC 123 CHI0018089 21-Oct-09 6.98 BC 123 CHI0031199 11-March -16 6.68 BC 123 NAP 10008 9 22 22-Jan-08 3.0 9 BC 123 NAP 1011123 21-DC -11 89.9 Q QQ 78 9 NAP 10008 9 22 22-Jan-08 4.01 QRT 1 Jawal 04000 9 -MAR-12 13.23 QRT 1 NAP19922 -Feb-08 08.09 QRT 1 NAP 10018 9 28 28- FEB-09 2.09 WW000 CHI0031208 11-Mar-13 200.01 WW000 CHI0031298 11-Mar -13 200.01 YZV11 JOL0200080 10-Oct-06 230.23 YZV11 AUR0700979 14-APR-13 6.68 YZV11 CHI0018189 03-Oct-06 556.98 YZV11 JOL0300080 10- March - 11 300  

You can use total tasks to simplify this type of query.

query :

  select sku, keep the maximum (price) (order first by dense_rank buy_date) first_purchase_price, Keep the maximum (price) (final order dense_rank by purchase_date) from store group by last_purchase_price sku;  

:

  | SKU | FIRST_PURCHASE_PRICE | LAST_PURCHASE_PRICE | | -------- | ---------------------- | ----------------- ---- | | Bc 123 3.09 | 6.68 | | QRT1 | 9.09 | 13.23 | | QQQ789 | 4.01 | 4.01 | | WW000 | 200.01 | 200.01 | | YZV11 | 230.23 | 6.68 |  

Comments

Popular posts from this blog

Member with no value in F# -

java - Joda Time Interval Not returning what I expect -

c# - Showing a SelectedItem's Property -