Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2010
    Posts
    40

    Unanswered: Extract max value for the group

    Hi to all,
    I am trying to find out a way to extract rows with a maximal value of the certain attribute in a group.
    For example the task is for a set of provinces which in turn are divided into a number of administrative units to list for each province only the administrative unit with the highest population. How can I do this?
    Thanks

  2. #2
    Join Date
    Nov 2006
    Posts
    82
    Could you kindly put here table structure you use with some sample rows and please specify with that data what do you want to achieve.

  3. #3
    Join Date
    Sep 2010
    Posts
    40
    Code:
    CREATE TABLE Admin_Unit
    (
    Population int NOT NULL,
    Area int NOT NULL,
    Position varachar(255),
    Ruling_party varchar(255),
    Legislation varchar(255),
    ....
    PRIMARY KEY (Ad_Id),
    FOREIGN KEY (P_Id) REFERENCES Province(P_Id)
    )
    Each province is composed of the administrative units described by the population, area, ruling party, legislation type etc.

    I am trying to find a way to select for each province an administrative unit with the largest population in a group of adminstarative units with the same ruling_part, legislation and area larger than certain limit value.
    How to do this?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do a web search for the row with the groupwise max

    the result which has ~jk in the title will give you 11 different ways of doing it

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •