Back

Answered by helix

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;

Answered by tymp

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;

Answered by bocalettirocco

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;

Answered by anonymousUser

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;