max value update

• scottichrosaviakosmos

  SSCommitted

  Points: 1889

  More actions

  September 30, 2015 at 11:32 am

  #303567

  create table countrydetails(Countryid int,country varchar(20),stateid int, states varchar(50),sales int)

  insert into countrydetails(countryid,country,stateid,states,sales)

  select 100,'UK',1,'England',1000

  union all

  select 100,'UK',2,'Scotland'2000

  union all

  select 100,'UK',3,'Ireland'1000

  union all

  select 101,'us',1,'Texas',5000

  union all

  select 101,'us',2,'Alabama',3000

  union all

  select 101,'us',3,'Alaska',4000

  union all

  select 102,'canada',1,'Quebec',7000

  union all

  select 102,'canada',2,'[British Columbia]',8000

  union all

  select 102,'canada',3,'[Nova Scotia]',2000

  union all

  select 102,'canada',4,'Manitoba',3000

  I am trying to update the sales of 2nd max stateid in a country with the max stateid sales value -3

  ex:

  update countrydetails

  set sales of 2nd max(stateid)= sales-3 of max(stateid)

  where countryid in (select countryid from jjj group by country having count(countryid)>1

  I did some try by but not able to get desired result. Can someone help me on this.

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  September 30, 2015 at 11:45 am

  #1830532

  ok...lets tidy this up a bit for you

  CREATE TABLE countrydetails(

  Countryid INT

  ,country VARCHAR(20)

  ,stateid INT

  ,states VARCHAR(50)

  ,sales INT

  );

  INSERT INTO countrydetails(Countryid,country,stateid,states,sales) VALUES (100,'UK',1,'England',1000);

  INSERT INTO countrydetails(Countryid,country,stateid,states,sales) VALUES (100,'UK',2,'Scotland',2000);

  INSERT INTO countrydetails(Countryid,country,stateid,states,sales) VALUES (100,'UK',3,'Ireland',1000);

  INSERT INTO countrydetails(Countryid,country,stateid,states,sales) VALUES (101,'us',1,'Texas',5000);

  INSERT INTO countrydetails(Countryid,country,stateid,states,sales) VALUES (101,'us',2,'Alabama',3000);

  INSERT INTO countrydetails(Countryid,country,stateid,states,sales) VALUES (101,'us',3,'Alaska',4000);

  INSERT INTO countrydetails(Countryid,country,stateid,states,sales) VALUES (102,'canada',1,'Quebec',7000);

  INSERT INTO countrydetails(Countryid,country,stateid,states,sales) VALUES (102,'canada',2,'[British Columbia]',8000);

  INSERT INTO countrydetails(Countryid,country,stateid,states,sales) VALUES (102,'canada',3,'[Nova Scotia]',2000);

  INSERT INTO countrydetails(Countryid,country,stateid,states,sales) VALUES (102,'canada',4,'Manitoba',3000);

  SELECT * FROM countrydetails

  delivers this

  +------------------------------------------------------------+

  ¦ Countryid ¦ country ¦ stateid ¦ states ¦ sales ¦

  ¦-----------+---------+---------+--------------------+-------¦

  ¦ 100 ¦ UK ¦ 1 ¦ England ¦ 1000 ¦

  ¦ 100 ¦ UK ¦ 2 ¦ Scotland ¦ 2000 ¦

  ¦ 100 ¦ UK ¦ 3 ¦ Ireland ¦ 1000 ¦

  ¦ 101 ¦ us ¦ 1 ¦ Texas ¦ 5000 ¦

  ¦ 101 ¦ us ¦ 2 ¦ Alabama ¦ 3000 ¦

  ¦ 101 ¦ us ¦ 3 ¦ Alaska ¦ 4000 ¦

  ¦ 102 ¦ canada ¦ 1 ¦ Quebec ¦ 7000 ¦

  ¦ 102 ¦ canada ¦ 2 ¦ [British Columbia] ¦ 8000 ¦

  ¦ 102 ¦ canada ¦ 3 ¦ [Nova Scotia] ¦ 2000 ¦

  ¦ 102 ¦ canada ¦ 4 ¦ Manitoba ¦ 3000 ¦

  +------------------------------------------------------------+

  what do you want your end results to be please?

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• scottichrosaviakosmos

  SSCommitted

  Points: 1889

  More actions

  September 30, 2015 at 11:56 am

  #1830540

  I want

  countryid country stateid state sales

  100 UK 2 scottland sales of stateid 3 -3(1000-3)

  .....

  ..... similar for all countries

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  September 30, 2015 at 11:59 am

  #1830542

  scottichrosaviakosmos (9/30/2015)

  ---

  I want

  countryid country stateid state sales

  100 UK 2 scottland sales of stateid 3 -3(1000-3)

  .....

  ..... similar for all countries

  ??

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  September 30, 2015 at 12:27 pm

  #1830549

  I am going to have a guess...as a starter

  is the following anywhere close?

  WITH cdets as (

  SELECT Countryid, country, stateid, states

  FROM countrydetails

  WHERE (stateid = 2)

  GROUP BY Countryid, country, stateid, states, sales

  )

  , sdets as (

  SELECT Countryid, sales - 3 AS results

  FROM countrydetails

  WHERE (stateid = 3)

  GROUP BY Countryid, sales - 3

  )

  SELECT cdets.Countryid, cdets.country, cdets.stateid, cdets.states, sdets.results

  FROM cdets INNER JOIN

  sdets ON cdets.Countryid = sdets.Countryid

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• scottichrosaviakosmos

  SSCommitted

  Points: 1889

  More actions

  September 30, 2015 at 1:11 pm

  #1830564

  ;with mycte as (

  select * ,lag(sales,1)Over(Partition by countryid Order by stateid DESC) salesLag

  , row_number() Over(Partition by countryid Order by stateid DESC) rn from countrydetails )

  select * from mycte where rn=2

  I think its working.