Question 249567 in Mysql Question posted in
The official MySQL documentation can be found here.

I am trying to find the highest hourly pay for each city in mysql,while my subquery is working my whole query is not working

  • My subquery = select *,rank() over(partition by city order by hourly_pay desc) from persons as a;

  • My whole query = select * from ( select *,rank() over(partition by city order by hourly_pay desc) from persons ) as b where rank=1;

  • the error i am getting in mysql is error code 1064,
    Table- "ID";"Name1";"dates";"hourly_pay";"city" 1;"Mohit";"2016-07-07";12000;"nashik" 2;"Vish";"2018-07-07";11000;"gurgaon" 3;"rishi";"2019-07-07";12000;"raipur" 4;"kartik";"2020-08-18";25000;"jaipur" 5;"stephan";"2010-04-18";17000;"bilaspur" 6;"brook";"2010-02-02";19000;"delhi" 7;"arjun";"2009-05-05";42000;"gurgaon" 8;"shub";"2005-04-05";52000;"chennai" 9;"joe";"2010-05-05";32000;"panji" 10;"wade";"2001-04-05";49000;"chandigar" 11;"karti";"2008-04-05";72000;"singapore" 12;"arjun";"2007-05-05";32000;"gurgaon" 13;"mokshi";"2005-05-05";33000;"mumbai" 14;"hema";"2005-05-05";60000;"deradun" 15;"drake";"2001-01-01";20000;"delhi"

ive also tried with

  • select * from ( select *,rank() over(partition by city order by hourly_pay desc) from persons as a ) as b where rank=1;

  • I was expecting to get the rows where the pay is highest for each city and for cities with only 1 person,to just return that row alone from that city

Tags:

2

Answers


  1. 0

    Give an alias to the Rank function:

    select * from 
    ( select *,rank() over(partition by city order by hourly_pay desc) as rank 
      from persons ) as b 
where rank=1;
    Login or Signup to reply.
  2. 0

    You can use a common table expression (CTE) to compute the ranks and then select the rows with rank=1 from the CTE.

    WITH RankedPersons AS (
    SELECT *,
           RANK() OVER (PARTITION BY city ORDER BY hourly_pay DESC) AS ranking
      FROM persons
)
SELECT *
  FROM RankedPersons
 WHERE ranking = 1;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search