select non duplicates by column from select query [duplicate]
This question already has an answer here:
SQL select only rows with max value on a column
30 answers
SQL: Group by minimum value in one field while selecting distinct rows
7 answers
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?
mysql sql
asked Dec 31 '18 at 21:19
yoranusyoranus
133
marked as duplicate by Shadow mysql
Users with the mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Jan 1 at 0:53
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
add a comment |
This question already has an answer here:
SQL select only rows with max value on a column
30 answers
SQL: Group by minimum value in one field while selecting distinct rows
7 answers
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?
mysql sql
asked Dec 31 '18 at 21:19
yoranusyoranus
133
marked as duplicate by Shadow mysql
Users with the mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Jan 1 at 0:53
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
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.0If 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 100Which 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
add a comment |
This question already has an answer here:
SQL select only rows with max value on a column
30 answers
SQL: Group by minimum value in one field while selecting distinct rows
7 answers
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?
mysql sql
asked Dec 31 '18 at 21:19
yoranusyoranus
133
This question already has an answer here:
SQL select only rows with max value on a column
30 answers
SQL: Group by minimum value in one field while selecting distinct rows
7 answers
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?
This question already has an answer here:
SQL select only rows with max value on a column
30 answers
SQL: Group by minimum value in one field while selecting distinct rows
7 answers
mysql sql
mysql sql
asked Dec 31 '18 at 21:19
yoranusyoranus
133
asked Dec 31 '18 at 21:19
yoranusyoranus
133
asked Dec 31 '18 at 21:19
yoranusyoranus
133
asked Dec 31 '18 at 21:19
yoranusyoranus
133
asked Dec 31 '18 at 21:19
yoranusyoranus
133
133
marked as duplicate by Shadow mysql
Users with the mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Jan 1 at 0:53
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
marked as duplicate by Shadow mysql
Users with the mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Jan 1 at 0:53
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
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.0If 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 100Which 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
add a comment |
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.0If 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 100Which 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
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
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
add a comment |
0
active
oldest
votes
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.0If 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 100Which 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