Struggling with some SQL in Oracle 11g.
I have a result set with group by and order by along with a MAX on a particular column, however results are not filtering out the results based on the max. I assume this is because of how I have the select statement set up, possibly the group by and order by. Maybe need to use a nested select or such. The query is as follows:
SELECT LV.LINE, LV.ZONE, LV.TRACK, TU.START_MARKER, TU.START_OFFSET, MAX(TU.TURNOUT_ID) AS TURNOUT_ID, TU.ASSET_TAG, TU.CLASS, TU.GPS_LONG, TU.GPS_LAT
CKS LV, CERREJON.V_TURNOUTS TU
WHERE TU.TRACK_ID = LV.TRACK_ID
AND (TU.TURNOUT_ID IS NOT NULL) AND (TU.TURNOUT_ID > 0)
--AND LV.ZONE = 'TRAMO 2'
GROUP BY LV.LINE, LV.ZONE, LV.TRACK, TU.START_MARKER, TU.START_OFFSET, TU.ASSET_TAG, TU.CLASS, TU.GPS_LONG, TU.GPS_LAT, TU.IMPORT_DATE
ORDER BY LV.LINE, LV.ZONE, LV.TRACK, TU.START_MARKER, TU.START_OFFSET, TU.ASSET_TAG DESC, TU.IMPORT_DATE DESC
The results are attached. The item in yellow is the MAX value for that result set (line, zone, track, start_marker, start_offset and asset_tag. This is the only row out of this result set I want to be included. the two rows following should not be included.