select non duplicates by column, fields don't correspond

Question

I was told to create a new question regarding one that apparently was duplicated and that if the duplicates didn't give me the correct answer I desired.

So the basics of my system is that I have a database populated with "house" positions/coordinates. I do an advanced query to select all of the houses near a certain coordinate 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;

This will result in;

+------+------+------------------------+
| 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 basically want to do is grab the rows that have the least distance but I do not want duplicates based on the "type" column, like so;

+------+------+------------------------+
| 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 |
+------+------+------------------------+

I received two answers (which were both wrong) in my previous question and they're half correct, one of the answers was;

"It is more simple with a nested query:

SELECT id,type, MIN(distance) AS 'minDistance'
FROM(
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) AS TABLE
GROUP BY type 
ORDER BY 'minDistance'

"

^ The answer above does select the correct distance that I expect. But what it doesn't do is match the id field and all of the other fields I would like to select, basically mismatching the row with different values. Not sure if I explained that correctly but here is basically what happens.

If my table is populated with these rows;

+------+------+------------------------+
| id   | type | distance               |
+------+------+------------------------+
| 1960 |    2 |     65.757256937390904 |
| 1953 |    2 | 0.00004489639611771451 |
| 2055 |    2 |      17.11486010149676 |
+------+------+------------------------+

And I run the query I got from the answer I would get this;

+------+------+------------------------+
| id   | type | distance               |
+------+------+------------------------+
| 1960 |    2 | 0.00004489639611771451 |
+------+------+------------------------+

But as you can see, the distance doesn't correspond with the correct id field, it is supposed to be id 1953 but instead its 1960.

How would I go on about correcting this?

PS; if you're interested in the other topic/question; select non duplicates by column from select query

Possible duplicate of [Get records with max value for each group of grouped SQL results](https://stackoverflow.com/questions/12102200/get-records-with-max-value-for-each-group-of-grouped-sql-results) — sticky bit, Jan 01 '19 at 20:37
how many houses do you have of each type? the normal answers for this problem are pretty inefficient, but there's a messy way that's better if it is worth the trouble to do (see https://stackoverflow.com/a/15422121/17389; but you'd have to make the distance string-sortable by rounding to say 6 digits after the decimal and LPADing assuming a max of say 5 digits before the decimal). (and of course window functions are the correct way to solve this problem, but I don't think mysql has those until version 8) — ysth, Jan 01 '19 at 21:17

Derviş Kayımbaşıoğlu · Accepted Answer · 2019-01-01T21:06:43.413

1

MySql 8 Compatible

WITH dist as (
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 
)
select * from dist d1
where distance = (
select  min(distance)
from dist d2
  where d1.type = d2.type
)

For Mysql 8<, you may consider to create view for your query like

CREATE VIEW DIST AS (
  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 
)

Then

select * from dist d1
where distance = (
select  min(distance)
from dist d2
  where d1.type = d2.type
)

edited Jan 01 '19 at 21:06

answered Jan 01 '19 at 20:45

Derviş Kayımbaşıoğlu

28,492
4
50
72

Hey, I'm getting an sql syntax error `ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'dist as ( SELECT id, type, distance FROM (SELECT b.id, b.type, b.x, b.y, b.z, ' at line 1` Version: `5.7.24-0ubuntu0.16.04.1` – yoranus Jan 01 '19 at 20:52
which version of mysql are you using? – Derviş Kayımbaşıoğlu Jan 01 '19 at 20:53
`5.7.24-0ubuntu0.16.04.1 ` – yoranus Jan 01 '19 at 20:54
the query I wrote is mysql 8 compatible – Derviş Kayımbaşıoğlu Jan 01 '19 at 21:03
1

This gave me exactly what I needed, I used the MySQL 8 compatible query as I was already intending of upgrading to 8, thanks a lot. – yoranus Jan 01 '19 at 21:50

score 0 · Answer 2 · answered Jan 01 '19 at 20:39

0

Just that you need a having clause

SELECT id,type, distance AS 'minDistance'
FROM(
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) AS TABLE
GROUP BY type having distance=min(distance) -- added
ORDER BY 'minDistance'

answered Jan 01 '19 at 20:39

Himanshu

3,830
2
10
29

I get a `ERROR 1054 (42S22): Unknown column 'distance' in 'having clause'` error. – yoranus Jan 01 '19 at 20:44

select non duplicates by column, fields don't correspond

2 Answers2