Selecting Max from subquery Oracle
<p>I’m using Oracle and trying to find the maximum transaction count (and associated date) for each station.</p>

<p>This is the code I have but it returns each transaction count and date for each station rather than just the maximum. If I take the date part out of the outer query it returns just the maximum transaction count for each station, but I need to know the date of when it happened. Does anyone know how to get it to work?</p>

<p>Thanks!</p>

<pre><code>SELECT STATION_ID, STATION_NAME, MAX(COUNTTRAN), TESTDATE
FROM 
(
    SELECT COUNT(TRANSACTION_ID) AS COUNTTRAN, STATION_ID, 
           STATION_NAME, TO_CHAR(TRANSACTION_DATE, 'HH24') AS TESTDATE
    FROM STATION_TRANSACTIONS 
    WHERE COUNTRY = 'GB' 
    GROUP BY STATION_ID, STATION_NAME, TO_CHAR(TRANSACTION_DATE, 'HH24')
)
GROUP BY STATION_ID, STATION_NAME, TESTDATE
ORDER BY MAX(COUNTTRAN) DESC
</code></pre>

<p>This image shows the results I currently get vs the ones I want:</p>

<p><a href="https://i.stack.imgur.com/LgHCv.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/LgHCv.png" alt="enter image description here"></a></p>