troubleshooting Question

Oracle Query using max with group by and order by

Avatar of 66chawger
66chawgerFlag for United States of America asked on
Oracle DatabaseSQL
33 Comments1 Solution154 ViewsLast Modified:
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
FROM CERREJON.V_LINES_ZONES_TRACKS 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.
SI_SQL_Results_Capture.JPG
ASKER CERTIFIED SOLUTION
66chawger

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 33 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 33 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros