Search code examples
sqldifferenceoracle-xe

SQL Query - Finding a difference between 2 values in same table

I need help with a specific query involving one of my databases.

Name           Elev    Difficulty  Map         Region
----------------------------------------------------------
Pilot Knob(S)   6200    2       Oynx          Southern Sierra
Spanish Needle  12322   3       Lament Peak   Southern Sierra
Lamont Peak     7429    2       Lament Peak   Southern Sierra
South Guard     13232   2       Mt Brewer     Great Western Divide
Mount Brewer    13570   2       Mt Brewer     Great Western Divide
North Guard     13327   4       Mt Brewer     Great Western Divide
Midway Mountain 11284   2       Mt Brewer     Great Western Divide

I need to find which maps have more than 2000 foot difference between the highest and lowest peaks?

Not to sure on I would use the MIN() and MAX() functions to figure this out. Help is much appreciated!

Solution

  • You’re on the right track with MIN and MAX. You just need to apply them over a grouping of the maps. Something like this:

    SELECT Map 
FROM 
    Table 
GROUP BY  
    Map
HAVING 
    MAX(Elev) - MIN(Elev) > 2000
ORDER BY
    MAX(Elev) - MIN(Elev)