SQL script help :)

[#1]

Guys,

if i have a table as shown below and i want to select :

1) The hour in which the profit is maxmium for each product ID.

2) Then Seelct the data ( selling proce & cost ) "just a single row" which corresponds to that mentioned hour.

How can i do this using the code in the script   

Day           Product ID          Hour          profit          Selling price             Cost

Sun                  1                   1:00            3                    10                         7

Sun                  1                   2:00            5                     15                        10

Sun                  1                   3:00            7                     27                        20

Sun                  1                   4:00            8                    38                         30

Sun                  1                   5:00          16                    32                         16

Sun                  2                   1:00            3                    10                         7

Sun                  2                   2:00            5                     15                        10

Sun                  2                   3:00            7                     27                        20

Sun                  2                   4:00            8                    38                         30

Sun                  2                   5:00          16                    32                         16

Sun                  3                   1:00            3                    10                         7

Sun                  3                   2:00            5                     15                        10

Sun                  3                   3:00            7                     27                        20

Sun                  3                   4:00            8                    38                         30

Sun                  3                   5:00          16                    32                         16

[rbecher]

Hi,

do you mean only the hour or the day and the hour?

- Ralf

[rbecher]

BTW, something like this:

Results:

LOAD Day & ProductID as %key, Day, ProductID, Hour, Profit, SellingPrice, Cost

Where not exists(%key, Day & ProductID);

SELECT Day, ProductID, Hour, Profit, SellingPrice, Cost

ORDER by Day, ProductID, Profit desc;

- Ralf

[Jason_Michaelides]

Assuming your data source is not an external database (if it was I'd probably group in the SQL query), and assuming you just mean hour and not Day and Hour:

Data_temp:

LOAD

     Day

     ,ProductID

     ,Hour

     ,profit

     ,[Selling price]

     ,Cost

FROM....;

Map_MaxProfit:

MAPPING LOAD DISTINCT

     ,ProductID

     ,Max(profit)  

RESIDENT Data_temp

GROUP BY ProductID;

Data:

LOAD

     Day

     ,ProductID

     ,Hour

     ,profit

     ,[Selling price]

     ,Cost

      ,IF(Profit=ApplyMap('Map_MaxProfit',ProductID),'Yes','No')     AS     [Max hour?]

RESIDENT Data_temp;

DROP TABLE Data_temp;

Hope this helps,

Jason

PS - not tested!

[#5 · Accepted solution]

Thanks jason it worked

[CELAMBARASAN]

Hi,

     If the solution solves your problem then close this discussion by mark it as answer.It will help others who search this community for the same type of problem.

Thanks,

Celambarasan

[Jason_Michaelides]

No problem. (Although I think you were supposed to mark my answer as correct, not your thanks )

Jason

[rbecher]

Have you tried my solutions? Seems to be much more compact and ressource efficient...

- Ralf