Jump to content

Getting rows with hightest value


AH_AH

Recommended Posts

Hi,

I need expert help to solve below problem.

My data in table 'mytable' look 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

 

I need to get below results:

customer country month cost

c001 DK 201506 -100

c001 FR 201507 -200

 

that is 'within each month', taking 'hightest 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.

 

Thanks for your help.

Regards

Nomann

 

 

Link to comment
Share on other sites

That's the lowest cost, -100 is less than -50, not greater than.

 

You can group by the month, country, and customer and select the minimum value for cost. That will return multiple records though, so you'll need to loop through them to figure out which one you want.

Link to comment
Share on other sites

thanks for responding.
I am writing like:
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"
this gives me result:
c001 US 201506 -100
c001 DK 201506 -100
c001 FR 201507 -200
but i need like:
c001 DK 201506 -100
c001 FR 201507 -200
OR
c001 US 201506 -100
c001 FR 201507 -200
how can i further enhance this SQL or should I write it in totally different way?
thanks
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...