cancel
Showing results for 
Search instead for 
Did you mean: 

SQL Script Calc. view: sql to get row with min. value

a_ahmad
Participant
0 Kudos

Hi,

I am developing a SQL calc. view. I reach to a level where I have my data like:

customer country month     cost
c001          US        
201506   -100
c001          DK         201506   -100
c001          DE         201506    -50
c001          FR         201507   -200
c001          UK         201507    -50

out of above dataset, I need to get below results:

customer country month     cost

c001     DK      201506   -100

c001     FR      201507   -200

that is 'within each month', taking 'lowest cost' generating 'country' for each customer.

CATCH: in this example, we can see that in 201506, cost -100 is same for US and DK. In this case only taking either one, not both.

WHAT I've tried so far:

SELECT "customer" as "custM", "month" as "monthM", "country" as "countryM", "cost" as "costM"

FROM "mytable" as "M"

INNER JOIN

     (

          SELECT "customer" as "custS", "month" as "monthS", MIN("costSUM") AS "costMIN"

          FROM

                    (SELECT distinct "customer", "country", "month", sum("cost") as "costSUM" FROM "mytable"

                    GROUP BY "customer", "country", "month")

          GROUP BY "customer","month"

     ) as "S"

ON "M"."customer" = "S"."custS" AND "M"."month" = "S"."monthS" AND "M"."cost"= "S"."costMIN"

the above SQL gives me result like:

c001     US      201506   -100
c001     DK      201506   -100
c001     FR      201507   -200

but i need either like:

c001     DK      201506   -100
c001     FR      201507   -200

OR like:

c001     US      201506   -100
c001     FR      201507   -200


I much appreciate your help how to write SQL to solve this issue.

best regards

Ahmad

Accepted Solutions (1)

Accepted Solutions (1)

pfefferf
Active Contributor
0 Kudos

Hello Ahmad,

one option would be:


select "customer", "country", "month", "cost" from (

  select "customer", "country", "month", min("cost") over (partition by "customer", "month" order by "cost") as "cost", row_number() over (partition by "customer", "month" order by "cost") as "row_num" from "mytable"

)

where "row_num" = 1;

Regards,

Florian

rindia
Active Contributor
0 Kudos

Query given by Florian Pfeffer works.

The small change to the query is:


select customer, country, month, cost from (

  select customer, country, month,  cost,

  row_number() over (partition by customer, month order by cost) as row_num from mytable

)

The difference is, not using aggregate function for cost.

Answers (1)

Answers (1)

muthuram_shanmugavel2
Contributor
0 Kudos

Hi Ahmed,

One more option

If you want result set contain DK:

Select Customer, Min(Country), Month, Cost

FROM "INF713775"."TABLE_JULY14"

Where (Month,Cost) IN (Select Month, Min(Cost) From "INF713775"."TABLE_JULY14"  Group By Month)

Group By Customer, Month, Cost


If you want result set contain US:


Select Customer, Max(Country), Month, Cost

FROM "INF713775"."TABLE_JULY14"

Where (Month,Cost) IN (Select Month, Min(Cost) From "INF713775"."TABLE_JULY14"  Group By Month)

Group By Customer, Month, Cost



Regards,

Muthuram