Selecting Unique records
I'm working on a Geo/Spatial search where I'm looking for nearby points. I have this Haversine query being run against a table:
SELECT
uid, adrLat, adrLng,
round(3956 * 2 * ASIN(SQRT(POWER(SIN((39.97780609 - abs(adrLat)) * pi() / 180 / 2), 2) + COS(39.97780609 * pi() / 180) * COS(abs(adrLat) * pi() / 180) * POWER(SIN((-105.25861359 - adrLng) * pi() / 180 / 2), 2))), 2) AS distance
FROM dataPoints
WHERE adrLng BETWEEN -105.2680699902 AND -105.2491571898
AND adrLat BETWEEN 39.970559713188 AND 39.985052466812
HAVING distance <= 0.30 and distance > 0.00
ORDER BY distance;
This would give me a result much like this:
+-----+-------------+---------------+----------+
| uid | adrLat | adrLng | distance |
+-----+-------------+---------------+----------+
| 191 | 39.97764587 | -105.25627136 | 0.12 |
| 520 | 39.97746658 | -105.25627136 | 0.13 |
| 265 | 39.97560120 | -105.25814056 | 0.15 |
| 266 | 39.97560120 | -105.25814056 | 0.15 |
| 274 | 39.97710037 | -105.25589752 | 0.15 |
| 98 | 39.97764969 | -105.26172638 | 0.17 |
| 576 | 39.97967911 | -105.25613403 | 0.18 |
| 575 | 39.97967911 开发者_开发问答| -105.25613403 | 0.18 |
| 469 | 39.97895813 | -105.25386810 | 0.26 |
| 470 | 39.97895813 | -105.25386810 | 0.26 |
| 1 | 39.98003006 | -105.25471497 | 0.26 |
| 383 | 39.97621155 | -105.26350403 | 0.28 |
| 431 | 39.97459793 | -105.25507355 | 0.29 |
| 430 | 39.97459793 | -105.25507355 | 0.29 |
| 429 | 39.97459793 | -105.25507355 | 0.29 |
| 428 | 39.97459793 | -105.25507355 | 0.29 |
+-----+-------------+---------------+----------+
However, as you can probably tell, some records are duplicated in the table (that's the way the data is provided to me, and I have to retain it that way.) 265:266, 576:575, 469:470, and 431-428 are all duplicates.
Is there a way to modify the query to pick unique records only? It looks like I have to match adrLat and adrLng to filter duplicates out, but I'm not sure if I can do it all within the same query, or if I have to do some post processing on the result.
SELECT adrLat, adrLng,
round(3956 * 2 * ASIN(SQRT(POWER(SIN((39.97780609 - abs(adrLat)) * pi() / 180 / 2), 2) + COS(39.97780609 * pi() / 180) * COS(abs(adrLat) * pi() / 180) * POWER(SIN((-105.25861359 - adrLng) * pi() / 180 / 2), 2))), 2) AS distance
FROM mytable
WHERE adrLng BETWEEN -105.2680699902 AND -105.2491571898
AND adrLat BETWEEN 39.970559713188 AND 39.985052466812
GROUP BY
adrLat, adrLng
HAVING distance <= 0.30
AND distance > 0.00
ORDER BY
distance
SELECT DISTINCT colum_name FROM table
The SELECT keyword allows us to grab all information from a column (or columns) on a table. This, of course, necessarily mean that there will be redundancies. What if we only want to select each DISTINCT element? This is easy to accomplish in SQL. All we need to do is to add DISTINCT after SELECT. The syntax is as follows:
I still need all four columns returned
You've already got the unique data there (e.g. uid 576 and 575 return the same coordinates - but the uid is obviously different).
Your definition of 'unique' is obviously different from ours - can you provide an example of wht you expect to see?
精彩评论