SELECT name, party, months_in_office FROM ( SELECT name, party, months_in_office, row_number() over (partition by party order by months_in_office desc) as by_party_order FROM ( SELECT party, name, entered_office, timestampdiff(MONTH, entered_office, now()) - 1 as months_in_office FROM us_senate ) x ) x WHERE x.by_party_order <= 2;
SELECT name, party, months_in_office FROM ( SELECT name, party, months_in_office, row_number() over (partition by party order by months_in_office desc) as by_party_order FROM ( SELECT party, name, entered_office, timestampdiff(MONTH, entered_office, now()) - 1 as months_in_office FROM us_senate ) x ) x WHERE x.by_party_order <= 2;
SELECT name, party, months_in_office FROM ( SELECT name, party, months_in_office, row_number() over (partition by party order by months_in_office desc) as by_party_order FROM ( SELECT party, name, entered_office, timestampdiff(MONTH, entered_office, now()) - 1 as months_in_office FROM us_senate ) x ) x WHERE x.by_party_order <= 2;
SELECT name, party, months_in_office FROM ( SELECT name, party, months_in_office, row_number() over (partition by party order by months_in_office desc) as by_party_order FROM ( SELECT party, name, entered_office, timestampdiff(MONTH, entered_office, now()) - 1 as months_in_office FROM us_senate ) x ) x WHERE x.by_party_order <= 2;