GSAK SQL help needed, please

[+Gill & Tony]

I would appreciate help with the best way to tackle this problem.

 

I have a database with two custom fields: "Nation" and "Region". Nation is effectively $d_Country, but breaks the UK into England, Scotland and Wales; and breaks Ireland into Northern Ireland and the Republic of Ireland. Region is usually $d_State, but sometimes uses $d_County.

 

The database is filtered to only include finds and is sorted into Elevation, descending within Region, ascending within Nation, ascending.

 

When I loop through the filter, a change in Region gives me the cache with the highest elevation in that region. However, a change in Nation only gives me the cache with the highest elevation in the alphabetically first state, not the highest in the Nation.

 

What I want to produce is one line with the highest in the nation, followed by one line for each region giving the highest in the region.

 

I could do two loops with different sorts, storing the Nation data on the first pass in an array, but I'm sure that there is some clever SQL which would get me the GCCode, Name and Elevation of the cache with the highest elevation of all caches with the same Nation value.

 

The Nation custom field is driven by an SQL CASE statement and the SQL is in a variable $NationSQL. I can get the current Nation value using:

$_Sql = "Select " + $NationSQL + " from caches where code='" + $d_Code + "'"

 

So, my questions are:

 

1. Is there a way to get the highest elevation by nation with a single SQL call?

 

2. If there is, there will also be a way to get the highest by region as well, so do I actually need to loop through all the finds, or can I do some real magic SQL and avoid the whole loop process. The problem with the loop is that I have to do the above SELECT statement for both Nation and Region for each cache to tell if I have hit a change point and that seems to be inefficient.

 

Any advice would be appreciated

 

Tony

[+The Jester]

That's a fairly technical GSAK/SQL question - I think you really need to ask in the GSAK forums, not here in the GC forums.

[+Gill & Tony]

That's a fairly technical GSAK/SQL question - I think you really need to ask in the GSAK forums, not here in the GC forums.

Yes, I realised I had posted it in the wrong forum but am on a cruise ship with limited internet, so I couldn't get back to delete it till now. I'm off to post it into the GSAK forum now.

 

Apologies

 

Tony

[+Viajero Perdido]

Kudos to The Jester for figuring out it's a GSAK question; that wasn't mentioned in the OP anywhere.

 

The only reason I'm in here (I know nothing about SQL) is because I was curious what the heck "SQ" is. Effective click bait; remember that technique.

[+kunarion]

Effective click bait; remember that technique.

Yes, let's "keep it on the SQ".

 

 

 

 

Edited December 8, 2016 by kunarion

[Keystone]

I've edited the topic title to say "GSAK SQL help needed, please" instead of "More SQ help needed, please."

 

In parts of the USA, "SQ" is the acronym for "Spirit Quest," a very large series of caches hidden in or near cemeteries. That's what made me click on the topic, as I would have been able to help explain the customs and standards for cache placements in that series.