Re: Access Distance Issue
Wow. Access! :D
Maybe, from what I read here: https://stackoverflow.com/questions/...correct-result
You might get MIN to work if you use "Val" ,of course there is no environment anywhere I could test this.
Re: Access Distance Issue
Access has many peculiarities. I had not seen that one. I don't believe it will work, but what the heck, might as well try it. The reason I don't believe it will work is that, while Min may not ensure things are a number (can't be an integer, as many of these will differ only by the fractional part), SQR certainly should. Therefore, SQR should serve the same purpose as Val in that example. Still, the only way to be certain is to test it.
Re: Access Distance Issue
In your original Query (or the second one), leave out the TOP 1 and put it in a Sub-Query using an ORDER BY there (!!) --> you receive ALL Datapoints Ordered acc. to your Criteria!
And from the OUTER SELECT you grab the Data from the ordered Subselect and THEN apply your TOP 1
Code:
SELECT TOP 1 Your Fields Here.... FROM
(SELECT ID, blablabl, MIN(Pythagoras) As DIST FROM MyTable WHERE SomeID=42 GROUP BY SomeFIelds ORDER BY MIN(Pythagoras))
Though i dare say the performance might be poor depending on how many Datapoints the Subselect is really returning.
IF the Subselect only returns a few hundred, you might not notice anything, if there are a few million datapoints though.....
test test test it... as always
Re: Access Distance Issue
I had a design kind of like that earlier, but not quite like that. Specifically, it left out the Order By in the sub-query. As it turns out, the size will not be an issue. Though the table itself is large, that WHERE statement will drop the number of results down to between about 2 and two dozen, which should be trivial.
Re: Access Distance Issue
I simplified that a bit by just ordering by the Pythagorean result. I knew that was possible, I was just hoping it wasn't necessary. Seems like extra work. Perhaps the query engine optimizes away the double calculation, but perhaps not.
Re: Access Distance Issue
Quote:
Originally Posted by
Shaggy Hiker
I simplified that a bit by just ordering by the Pythagorean result. I knew that was possible, I was just hoping it wasn't necessary. Seems like extra work. Perhaps the query engine optimizes away the double calculation, but perhaps not.
Could be (untested) since the ORDER BY is the last operation of an SQL-statement, and at that point the calculated value is known.
IIRC, in the ORDER BY you should be able to use the Alias of the „Field“
Re: Access Distance Issue
I've just reread the Order of Execution for an SQL-Statement:
- FROM and/or JOIN clause.
- WHERE clause.
- GROUP BY clause.
- HAVING clause.
- SELECT clause.
- DISTINCT clause.
- ORDER BY clause.
- LIMIT and/or OFFSET clause.
That said: You shouldn't need the Sub-select.
The ORDER BY is the second-to-last, followed by the Limit (TOP X in Access/MS SQL).
And i just did a test with SQLite: You can definitely use the Field-Alias of the Aggregate in the ORDER-Clause
Code:
SELECT TOP 1 HName,RouteGUID,Lat_WGS84,Lon_WGS84,
MIN(SQR(((" & latitude.ToString & " - Lat_WGS84) * (" & latitude.ToString & " - Lat_WGS84))
+ ((" & longitude.ToString & " - Lon_WGS84) * (" & longitude.ToString & " - Lon_WGS84)))) AS Dist
FROM GIS_Routing
WHERE GIS_Routing.LLID = '" & LLID & "'
GROUP BY HName, RouteGUID,Lat_WGS84,Lon_WGS84
ORDER BY Dist
should do it.
OTOH, it's MS Access we are talking about, so there....
Re: Access Distance Issue
Yeah, I was thinking I could use the alias, but that does not work. That throws an exception about no value given for one or more parameters, so that alias...well, it is no longer an alias by the Order By phrase.
In any case, it works with the full mess in the Order By statement. Whether it does the calculations twice or not, I can't say, but there are so few records returned that it doesn't really matter.