CODEWITHSUNDEEP
sqldifferenceoracle-xe
Scott
Scott

Reputation: 465

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!

Upvotes: 0

Views: 738

Answers (2)

Adam
Adam

Reputation: 51

Depending on your implementation (I've demonstrated writing in T-SQL below. You need to aggregate the maps into groups, then evaluate these based on the maximum and minimum values within these groupings.

--//We're grouping by map and demonstrating the max and min values returned
select Map, max (elev) as MaxElev, min (elev) as MinElev
from targettable
group by map;

--//We then need to filter the group and only return when the difference is
--//Greater than 2000
select map,  max (elev) as MaxElev, min (elev) as MinElev
from targettable
group by map
having (Max (elev) - 2000) > min (Elev);

--//Or alternatively you can use subtract values and compare

select map,  max (elev) as MaxElev, min (elev) as MinElev
from targettable
group by map
having (max (elev) - min (elev)) > 2000;

--//We can then tidy up and add an order if required (descending shown)

select map
from targettable
group by map
having (max (elev) - min (elev)) > 2000
order by max(elev);

Upvotes: 0

user2316154
user2316154

Reputation: 334

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)

Upvotes: 2

Related Questions