CODEWITHSUNDEEP
mysqlsql
Talisin
Talisin

Reputation: 2480

Filtering out all but one result per group out of similar results

So I have a table that lists all the prime_ministers and what year they served and what party they were in.

I need the 3 bits of information; Their name, their first year of office and party in their final year of office (some switched parties).

The table looks a bit like this

min_nr|pm_name      |party           |yr_comm  
---------------------------------------------- 
1      Barton E      Protectionist    1901  
.  
.  
.  
11     Hughes W M    Labour           1915  
12     Hughes W M    National Labour  1916  
13     Hughes W M    Nationalists     1917  
14     Hughes W M    Nationalists     1918  
.  
.

I have done lots of searches but I don't know how to type this in to get a result.

Mr Woo did it in the comments section. It turns out I dont understand multiple JOINS and the correct usage of AND in ON.

SELECT a.pm_name, a.party, c.minYear
FROM  Table1 a 
      INNER JOIN
      (
          SELECT pm_name, MAX(yr_comm) maxYear
          FROM table1
          GROUP BY pm_name
      ) b ON a.pm_name = b.pm_name AND
             a.yr_comm = b.maxYear
      INNER JOIN
      (
        SELECT pm_name, MIN(yr_comm) minYear
        FROM table1
        GROUP BY  pm_name
      ) c ON a.pm_name = c.Pm_name

Edits, many many edits

Upvotes: 3

Views: 108

Answers (1)

John Woo
John Woo

Reputation: 263693

Try a subquery to get the maximum year for each prime minister. This works if you want to get all columns.

SELECT a.*
FROM tableName a INNER JOIN
        (
            SELECT ID, MAX(`year`) maxYear
            FROM tableName
            GROUP BY ID
        ) b ON a.ID = b.ID AND
                a.`Year` = b.maxYear

tableName => change it to the name of your table
ID => change it to the primary key
Year => change it to the column name for your year.

but if you onlyu want to get the ID of the prime minister and what year is their final year. You can do it directly without subquery.

SELECT ID, MAX(`year`) finalYear
FROM tableName
GROUP BY ID

PS: if this answer is still not clear, please add the schema of your table, sample records, and desired result and we can work on it. Thanks.

UPDATE 1

SELECT a.pm_name, a.party, c.minYear
FROM  Table1 a 
      INNER JOIN
      (
          SELECT pm_name, MAX(yr_comm) maxYear
          FROM table1
          GROUP BY pm_name
      ) b ON a.pm_name = b.pm_name AND
             a.yr_comm = b.maxYear
      INNER JOIN
      (
        SELECT pm_name, MIN(yr_comm) minYear
        FROM table1
        GROUP BY  pm_name
      ) c ON a.pm_name = c.Pm_name

SQLFiddle Demo

Upvotes: 4

Related Questions