CODEWITHSUNDEEP
sqlderby
user7847560
user7847560

Reputation:

Apache Derby extracting the maximum value from a table

I have the following code:

SELECT
  hora,
  SUM(cada_hora) AS suma_hora
FROM
  info
GROUP BY
  hora;

Which outputs a table with all the hours (from 0 to 23) along with their respective sums, like this;

Hora | Suma_hora
-----------------
0    | 100000
1    | 200000
2    | 150000
3    | 450000
4    | 120000
...  | ...

Now what I'd like to do is to extract from that table the one row with the maximum value for Suma_hora and show the hour and the respective max value. So it would look like this:

Hora | Max_hora
-----------------
3    | 450000

The closest I can get to my desired answer is this, but unfortunately I don't know how to get it to display the respective hour as well (it only shows the max value, i.e., Max_hora):

SELECT
  MAX(suma_hora) AS Max_hora
FROM
  (
    SELECT
      hora,
      SUM(cada_hora) AS suma_hora
    FROM
      info
    GROUP BY
      hora
  ) AS resultado;

Any help is greatly appreciated!

NB: Adding the following bit gives an error, so it doesn't work:

SELECT
  hora, MAX(suma_hora) AS Max_hora
...

Upvotes: 2

Views: 407

Answers (1)

developer_hatch
developer_hatch

Reputation: 16224

Simple as pie amigo mío :) just order by hora, and take the first, like this:

SELECT
  hora,
  SUM(cada_hora) AS suma_hora
FROM
  info
GROUP BY
  hora
Order by suma_hora DESC Limit 1;

Upvotes: 1

Related Questions