1

I've looked all over stackoverflow but without any luck, so here goes nothing.

I have a table populated with certain information on house positions, I select these positions and calculate the distance between the house coordinate and my desired coordinate, which I then order by distance ascending like so;

SELECT id, type, distance FROM (SELECT b.id, b.type, b.x, b.y, b.z, 
    SQRT(POWER(ABS(1654.5413 - b.x), 2) + POWER(ABS(-2293.7571 - b.y), 2) + POWER(ABS(-1.1996 - b.z), 2)) AS "distance" 
        FROM businesses b ORDER BY distance ASC) as T;

Example output;

+------+------+------------------------+
| id   | type | distance               |
+------+------+------------------------+
| 1953 |    2 | 0.00004489639611771451 |
|    2 |  100 |      8.757256937390904 |
| 1959 |    2 |      8.999959765646956 |
| 1960 |    2 |     10.499959765643807 |
| 1961 |    2 |     11.999959765641446 |
| 1962 |    2 |     13.499959765639607 |
| 1963 |    2 |     14.999959765638138 |
| 1964 |    2 |     16.499959765636934 |
| 2055 |    3 |      17.11486010149676 |
| 2054 |    1 |     17.751048488860313 |
| 1965 |    2 |     17.999959765635932 |
| 1966 |    2 |     19.499959765635083 |
| 1967 |    2 |     20.999959765634358 |
| 2056 |    5 |      22.26658275782834 |
| 1968 |    2 |     22.499959765633726 |
| 1969 |    2 |     23.999959765633175 |
| 2057 |    5 |     24.054132659013334 |
| 1970 |    2 |      25.49995976563269 |
| 2058 |    5 |     26.001138245767084 |
| 2061 |    4 |     26.853239370669378 |
| 1971 |    2 |      26.99995976563226 |
| 1972 |    2 |      28.49995976563187 |
| 2060 |    5 |      28.55999771765475 |
| 1973 |    2 |     29.999959765631523 |
| 2059 |    5 |     31.414688663981224 |
| 1974 |    2 |     31.499959765631207 |
|    1 |  100 |      121468.4587678613 |
+------+------+------------------------+

What I want to do with these results is only grab one row by selecting the non duplicates of the "type" column, like so (and keep the distance ASC order);

+------+------+------------------------+
| id   | type | distance               |
+------+------+------------------------+
| 1953 |    2 | 0.00004489639611771451 |
|    2 |  100 |      8.757256937390904 |
| 2055 |    3 |      17.11486010149676 |
| 2054 |    1 |     17.751048488860313 |
| 2056 |    5 |      22.26658275782834 |
| 2061 |    4 |     26.853239370669378 |
+------+------+------------------------+

If I attempt to "SELECT DISTINCT TYPE" it will not keep the order of the rows and will always select the last duplicate of "type" (I think I said that correctly).

How would I go about getting my desired result?

yoranus
  • 45
  • 4
  • Hey, Thanks for the answers, unfortunately = If I have two rows with the same type, let's say '2'; `Row #1: ID 1953 Distance 0.5 Row #2: ID 1960 Distance 35.0` If I set the X coordinate of the first row (ID 1953) to something else it will select the right distance, but mismatch the ID. So the results that both queries output will look something like this; `Row #1: ID 1953 Distance 35.0 Row #2: ID 1960 Distance 100` Which isn't what I really want, because the 2nd row should be ID 1953 and the first row should be ID 1960. – yoranus Dec 31 '18 at 22:28

0 Answers0