Back

Answered by Waytan

select sq.name, sq.party, sq.months_in_office
from
  (SELECT name, party, entered_office,
  TIMESTAMPDIFF(month, entered_office, CURDATE()) - 27 AS months_in_office,
      rank() over(partition by party order by
              TIMESTAMPDIFF(month, entered_office, CURDATE()) - 27 desc
             ) as 'rank'
  FROM us_senate) as sq
 where sq.rank <= 2
;

Answered by maryam-bibi

select sq.name, sq.party, sq.months_in_office
from
  (SELECT name, party, entered_office,
  TIMESTAMPDIFF(month, entered_office, CURDATE()) - 27 AS months_in_office,
      rank() over(partition by party order by
              TIMESTAMPDIFF(month, entered_office, CURDATE()) - 27 desc
             ) as 'rank'
  FROM us_senate) as sq
 where sq.rank <= 2
;

Answered by jonalexander

select sq.name, sq.party, sq.months_in_office
from
  (SELECT name, party, entered_office,
  TIMESTAMPDIFF(month, entered_office, CURDATE()) - 27 AS months_in_office,
      rank() over(partition by party order by
              TIMESTAMPDIFF(month, entered_office, CURDATE()) - 27 desc
             ) as 'rank'
  FROM us_senate) as sq
 where sq.rank <= 2
;

Answered by gurneet kaur

select sq.name, sq.party, sq.months_in_office
from
  (SELECT name, party, entered_office,
  TIMESTAMPDIFF(month, entered_office, CURDATE()) - 27 AS months_in_office,
      rank() over(partition by party order by
              TIMESTAMPDIFF(month, entered_office, CURDATE()) - 27 desc
             ) as 'rank'
  FROM us_senate) as sq
 where sq.rank <= 2
;

Answered by charlie1404

select sq.name, sq.party, sq.months_in_office
from
  (SELECT name, party, entered_office,
  TIMESTAMPDIFF(month, entered_office, CURDATE()) - 27 AS months_in_office,
      rank() over(partition by party order by
              TIMESTAMPDIFF(month, entered_office, CURDATE()) - 27 desc
             ) as 'rank'
  FROM us_senate) as sq
 where sq.rank <= 2
;

Answered by Pfwd44

select sq.name, sq.party, sq.months_in_office
from
  (SELECT name, party, entered_office,
  TIMESTAMPDIFF(month, entered_office, CURDATE()) - 27 AS months_in_office,
      rank() over(partition by party order by
              TIMESTAMPDIFF(month, entered_office, CURDATE()) - 27 desc
             ) as 'rank'
  FROM us_senate) as sq
 where sq.rank <= 2
;

Answered by abhi

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 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;