|
zeeshan13
Constraint Violating Yak Guru
347 Posts |
 Posted - 2010-06-07 : 19:40:12
|
| Hi All,I have table call Aggregate with following fileds. I am putting all records for some of the AggregateID's as an example. I have ordered the records by AggregateID.AggregateID, Description, ProductID, ParentID, SumofSalesA0000003065, SMALL CAMEO-PRODUCE, 000000083066, 2641.92A0000004012, NAVEL ORANGE FANCY JUMBO-PRODUCE, 000000083107, 61122.17A0000004012, NAVEL ORANGE FANCY JUMBO-PRODUCE, 000000003372, 442.53A0022700093, CG LINE EXCT-.02 OZ, 002270009338, 138.00A0022700093, CG LINE EXCT-.02 OZ, 002270009340, 114.00A0022700093, CG LINE EXCT-.02 OZ, 002270009339, 99.00A0027000909, CG VLMEXCT MAS-.24 OZ, 002270009097, 298.00A0027000909, CG VLMEXCT MAS-.24 OZ, 002270009096, 298.00As you can see in my above example there are 6 distinct Aggregate ID's.I want to have a SELECT query with a new column called "ParentID". ParentID would be same as ProductID but will be based on highest SumofSales for each set of Aggregate ID. So for the above records my SELECT should return the following records.AggregateID, Description, ProductID, ParentID, SumofSales, ParentIDA0000003065, SMALL CAMEO-PRODUCE, 000000083066, 2641.92, 000000083066A0000004012, NAVEL ORANGE FANCY JUMBO-PRODUCE, 000000083107, 61122.17, 000000083107A0000004012, NAVEL ORANGE FANCY JUMBO-PRODUCE, 000000003372, 442.53, 000000083107A0022700093, CG LINE EXCT-.02 OZ, 002270009340, 114.00, 002270009338A0022700093, CG LINE EXCT-.02 OZ, 002270009338, 138.00, 002270009338A0022700093, CG LINE EXCT-.02 OZ, 002270009339, 99.00, 002270009338A0027000909, CG VLMEXCT MAS-.24 OZ, 002270009097, 298.00, 002270009097A0027000909, CG VLMEXCT MAS-.24 OZ, 002270009096, 298.00, 002270009097As you see above in my output each set of Aggregate should always have one same ParentID which is determined by highest sales. So the SELECT query should check and do the following.a) If there is only one record for an AggregateID, choose the ProductID as a ParentID for that AggregateID record (that is the case in case of A0000003065 AggregateID). b) If there are multiple records for an AggregateID, choose the ProductID as a ParentID for respective AggregateID records with the highest SumofSales (ParentID 000000083107 for AggregateID A0000004012. ParentID 002270009338 for AggregateID A0022700093. )c) If all the ProductID's of a same AggregateID has same SumofSales, randomly choose on of the ProductID within that AggregateID as a ParentID (002270009097 ParentID for AggregateID A0027000909). How can I do that in a select query please suggest promptly. Thanks a lot for all your valueable help and time.Thanks,Zee |
|
Help with a SELECT query consisting one table!