Back

Answered by derrickmcmullin

select * from (
  select name, 
      party, 
      TIMESTAMPDIFF(MONTH, entered_office, CURDATE()) - 41 AS months_in_office 
  from us_senate
  where party = "independent"
  order by months_in_office desc
  limit 2
) as ind
  
union all

select * from (
  select name, 
      party, 
      TIMESTAMPDIFF(MONTH, entered_office, CURDATE()) - 41 AS months_in_office 
  from us_senate
  where party = "republican"
  order by months_in_office desc
  limit 2
) as rep

union all

select * from (
  select name, 
      party, 
      TIMESTAMPDIFF(MONTH, entered_office, CURDATE()) - 41 AS months_in_office 
  from us_senate
  where party = "democrat"
  order by months_in_office desc
  limit 2
) as dem

ORDER BY party;

Answered by jcr101402

select * from (
  select name, 
      party, 
      TIMESTAMPDIFF(MONTH, entered_office, CURDATE()) - 41 AS months_in_office 
  from us_senate
  where party = "independent"
  order by months_in_office desc
  limit 2
) as ind
  
union all

select * from (
  select name, 
      party, 
      TIMESTAMPDIFF(MONTH, entered_office, CURDATE()) - 41 AS months_in_office 
  from us_senate
  where party = "republican"
  order by months_in_office desc
  limit 2
) as rep

union all

select * from (
  select name, 
      party, 
      TIMESTAMPDIFF(MONTH, entered_office, CURDATE()) - 41 AS months_in_office 
  from us_senate
  where party = "democrat"
  order by months_in_office desc
  limit 2
) as dem

ORDER BY party;

Answered by battlethechamp

SELECT
    t1.name,
    t1.party,
    TIMESTAMPDIFF(month, t1.entered_office, "2021-10-28") AS months_in_office
FROM
    us_senate t1
WHERE
    (
        SELECT COUNT(*)
        FROM us_senate t2
        WHERE t2.party = t1.party
          AND TIMESTAMPDIFF(month, t2.entered_office, "2021-10-28") >= TIMESTAMPDIFF(month, t1.entered_office, "2021-10-28")
    ) <= 2
ORDER BY
    t1.party,
    months_in_office DESC;

Answered by vick0123

SELECT
    t1.name,
    t1.party,
    TIMESTAMPDIFF(month, t1.entered_office, "2021-10-28") AS months_in_office
FROM
    us_senate t1
WHERE
    (
        SELECT COUNT(*)
        FROM us_senate t2
        WHERE t2.party = t1.party
          AND TIMESTAMPDIFF(month, t2.entered_office, "2021-10-28") >= TIMESTAMPDIFF(month, t1.entered_office, "2021-10-28")
    ) <= 2
ORDER BY
    t1.party,
    months_in_office DESC;

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;