-2

I am trying to display a row or multiple rows having the maximum salary, I have tried the following things. I want to display at least the name and the max(salary) but I am unable to do so.

I tried to execute this code snippet but it shows me the first row instead of showing the one which has the maximum salary.

select * from instructor
having max(salary);

I also tried

select name, max(salary) from instructor;

But this returned the name from the first row and the maximum salary value from the whole column.

insaan
  • 29
  • 1
  • 8
  • 2
    MySQL or Oracle? Please tag only one database. – GMB Oct 09 '20 at 07:35
  • 2
    Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Use images only for what cannot be expressed as text or to augment text. Include a legend/key & explanation with an image. – philipxy Oct 09 '20 at 07:35
  • See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query – Strawberry Oct 09 '20 at 07:35
  • Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code in table format. [ask] Pause work on the overall goal, chop code to the 1st expression not giving what you expect & say what you expect & why. – philipxy Oct 09 '20 at 07:36
  • 1
    (Clearly,) When stated clearly this will be a faq. Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. Reflect your research. See [ask] & the voting arrow mouseover texts. If you post a question, use one phrasing as title. – philipxy Oct 09 '20 at 07:37
  • Okay, I will keep that in mind if I ask something next time. Sorry, I didn't do my research before asking a question. I am new MySQL and don't have much knowledge about it. – insaan Oct 09 '20 at 07:46
  • The principle applies to all technology, regardless of one's affinity with it. – Strawberry Oct 09 '20 at 08:46

1 Answers1

0

If you are running Oracle, I would recommend a fetch clause:

select *
from instructor
order by salary desc
fetch first row with ties

An alternative uses a correlated subquery (this is supported in Oracle, and in any version of MySQL):

select *
from instructor
where salary = (select max(salary) from instructor)

Alternatively, you can use window functions (this requires MySQL 8.0)

select *
from (select i.*, rank() over(order by salary desc) rn from instructor) i
where rn = 1
GMB
  • 216,147
  • 25
  • 84
  • 135
  • 1
    Sorry I was a bit confused earlier between Oracle and MySQL, but I am using MySQL and subquery command worked for me. Thank you. – insaan Oct 09 '20 at 08:03