CODEWITHSUNDEEP
sql
Furkan Karacan
Furkan Karacan

Reputation: 192

SQL Find Max Value and Number of Rows Whic Are Equal to That Value

Given a table, I am trying to find Max Months * Hackos and number of ID's with the max Months * Hackos value.

I tried the below code but it seems we cant use Max clause in the inner select.

SELECT MAX(MONTHS * HACKOS) AS MAXIMUM_HACKOS,
(SELECT COUNT(ID) FROM HACKER WHERE MONTHS*HACKOS = MAX(MONTHS*HACKOS)) AS NUMBER_OF_HACKERS
FROM HACKER ;

Correct expected output is 144 and 2 but my code is wrong.

Sample Input

Upvotes: 1

Views: 8310

Answers (4)

Swapnil Saurav
Swapnil Saurav

Reputation: 1

Try this

SELECT  NAME FROM HACKER GROUP BY  ID, NAME, MONTHS, MONTHS * HACKOS having MONTHS * HACKOS  > 100  AND MONTHS < 10   order by ID

Upvotes: 0

Govind
Govind

Reputation: 439

Can this solve your problem?

SELECT ID, NAME, MAX(MAXIMUM_HACKOS) FROM ( SELECT ID, NAME, ( MONTHS * HACKOS) as MAXIMUM_HACKOS FROM HACKER) A LIMIT 1

Upvotes: 1

forpas
forpas

Reputation: 164154

You can use this query:

SELECT MAX(MONTHS * HACKOS) FROM HACKER

in the WHERE clause and then aggregate:

SELECT MAX(MONTHS * HACKOS) AS MAXIMUM_HACKOS,
       COUNT(*) AS NUMBER_OF_HACKERS
FROM HACKER
WHERE MONTHS * HACKOS = (SELECT MAX(MONTHS * HACKOS) FROM HACKER)

Upvotes: 2

allmhuran
allmhuran

Reputation: 4464

select     top 1
           MONTHS * HACKOS,
           count(*)
from       HACKER
group by   MONTHS * HACKOS
order by   MONTHS * HACKOS desc

Upvotes: 1

Related Questions