MySQL distinct query, result into another table
I have two field lat and lng in tbl1.
I just like to select only the unique numbers from both field and I like to place the result in tbl2 in the fields with the same name lat and lng.
Sorry about my inaccuracy.
Tbl1 is a square grid of coordinates, that is, the latitude coordinate is the same on each row of the grid, and the longitude coordinate is the same on each column, respectively. So there are as many different longitude and latitude coordinates in the result. Example - if grid is 100 x 100, tbl1 has 10000 rows and result table tbl2 has 100 rows or even smaller grid 2 x 2:
tbl1:
lat,lng,some more fields,..
65.123456,24.123456,..
65.123456,24.123567,..
65.123567,24.123456,..
65.123567,24.123567,..
tbl2:
lat,lng
65.123456,24.123456
65.123567,24.123567
I have tried the following:
insert into tbl2 (lat, lng) select distinct lat, lng from tbl1;
and I cant use
.. select distinct lat from tbl1 union select distinct lng from tbl1;
because it only returns one field.
mysql distinct
asked Dec 31 '18 at 12:57
hj2018hj2018
11
add a comment |
I have two field lat and lng in tbl1.
I just like to select only the unique numbers from both field and I like to place the result in tbl2 in the fields with the same name lat and lng.
Sorry about my inaccuracy.
Tbl1 is a square grid of coordinates, that is, the latitude coordinate is the same on each row of the grid, and the longitude coordinate is the same on each column, respectively. So there are as many different longitude and latitude coordinates in the result. Example - if grid is 100 x 100, tbl1 has 10000 rows and result table tbl2 has 100 rows or even smaller grid 2 x 2:
tbl1:
lat,lng,some more fields,..
65.123456,24.123456,..
65.123456,24.123567,..
65.123567,24.123456,..
65.123567,24.123567,..
tbl2:
lat,lng
65.123456,24.123456
65.123567,24.123567
I have tried the following:
insert into tbl2 (lat, lng) select distinct lat, lng from tbl1;
and I cant use
.. select distinct lat from tbl1 union select distinct lng from tbl1;
because it only returns one field.
mysql distinct
asked Dec 31 '18 at 12:57
hj2018hj2018
11
2
What have you tried? Where are you stuck? Do you know how to useDISTINCTin aSELECTquery? Do you know how to useSELECTin anINSERTquery?
– David
Dec 31 '18 at 13:00
1
Welcome to Stack Overflow. When posting any SQL questions, it is important to provide a small sample of rows from each related table. In your case, provide a sample fromtbl1and a sample of what the result intbl2should be, given the sample fromtbl1. You should also show us any code you have already tried, as we are happy to help where stuck but few contributors are willing to provide free coding service with no prior effort.
– Michael Berkowski
Dec 31 '18 at 13:00
add a comment |
I have two field lat and lng in tbl1.
I just like to select only the unique numbers from both field and I like to place the result in tbl2 in the fields with the same name lat and lng.
Sorry about my inaccuracy.
Tbl1 is a square grid of coordinates, that is, the latitude coordinate is the same on each row of the grid, and the longitude coordinate is the same on each column, respectively. So there are as many different longitude and latitude coordinates in the result. Example - if grid is 100 x 100, tbl1 has 10000 rows and result table tbl2 has 100 rows or even smaller grid 2 x 2:
tbl1:
lat,lng,some more fields,..
65.123456,24.123456,..
65.123456,24.123567,..
65.123567,24.123456,..
65.123567,24.123567,..
tbl2:
lat,lng
65.123456,24.123456
65.123567,24.123567
I have tried the following:
insert into tbl2 (lat, lng) select distinct lat, lng from tbl1;
and I cant use
.. select distinct lat from tbl1 union select distinct lng from tbl1;
because it only returns one field.
mysql distinct
asked Dec 31 '18 at 12:57
hj2018hj2018
11
I have two field lat and lng in tbl1.
I just like to select only the unique numbers from both field and I like to place the result in tbl2 in the fields with the same name lat and lng.
Sorry about my inaccuracy.
Tbl1 is a square grid of coordinates, that is, the latitude coordinate is the same on each row of the grid, and the longitude coordinate is the same on each column, respectively. So there are as many different longitude and latitude coordinates in the result. Example - if grid is 100 x 100, tbl1 has 10000 rows and result table tbl2 has 100 rows or even smaller grid 2 x 2:
tbl1:
lat,lng,some more fields,..
65.123456,24.123456,..
65.123456,24.123567,..
65.123567,24.123456,..
65.123567,24.123567,..
tbl2:
lat,lng
65.123456,24.123456
65.123567,24.123567
I have tried the following:
insert into tbl2 (lat, lng) select distinct lat, lng from tbl1;
and I cant use
.. select distinct lat from tbl1 union select distinct lng from tbl1;
because it only returns one field.
mysql distinct
mysql distinct
asked Dec 31 '18 at 12:57
hj2018hj2018
11
asked Dec 31 '18 at 12:57
hj2018hj2018
11
edited Jan 1 at 9:46
hj2018
asked Dec 31 '18 at 12:57
hj2018hj2018
11
asked Dec 31 '18 at 12:57
hj2018hj2018
11
asked Dec 31 '18 at 12:57
hj2018hj2018
11
11
2
What have you tried? Where are you stuck? Do you know how to useDISTINCTin aSELECTquery? Do you know how to useSELECTin anINSERTquery?
– David
Dec 31 '18 at 13:00
1
Welcome to Stack Overflow. When posting any SQL questions, it is important to provide a small sample of rows from each related table. In your case, provide a sample fromtbl1and a sample of what the result intbl2should be, given the sample fromtbl1. You should also show us any code you have already tried, as we are happy to help where stuck but few contributors are willing to provide free coding service with no prior effort.
– Michael Berkowski
Dec 31 '18 at 13:00
add a comment |
2
What have you tried? Where are you stuck? Do you know how to useDISTINCTin aSELECTquery? Do you know how to useSELECTin anINSERTquery?
– David
Dec 31 '18 at 13:00
1
Welcome to Stack Overflow. When posting any SQL questions, it is important to provide a small sample of rows from each related table. In your case, provide a sample fromtbl1and a sample of what the result intbl2should be, given the sample fromtbl1. You should also show us any code you have already tried, as we are happy to help where stuck but few contributors are willing to provide free coding service with no prior effort.
– Michael Berkowski
Dec 31 '18 at 13:00
2
2
What have you tried? Where are you stuck? Do you know how to use
DISTINCT in a SELECT query? Do you know how to use SELECT in an INSERT query?– David
Dec 31 '18 at 13:00
What have you tried? Where are you stuck? Do you know how to use
DISTINCT in a SELECT query? Do you know how to use SELECT in an INSERT query?– David
Dec 31 '18 at 13:00
1
1
Welcome to Stack Overflow. When posting any SQL questions, it is important to provide a small sample of rows from each related table. In your case, provide a sample from
tbl1 and a sample of what the result in tbl2 should be, given the sample from tbl1. You should also show us any code you have already tried, as we are happy to help where stuck but few contributors are willing to provide free coding service with no prior effort.– Michael Berkowski
Dec 31 '18 at 13:00
Welcome to Stack Overflow. When posting any SQL questions, it is important to provide a small sample of rows from each related table. In your case, provide a sample from
tbl1 and a sample of what the result in tbl2 should be, given the sample from tbl1. You should also show us any code you have already tried, as we are happy to help where stuck but few contributors are willing to provide free coding service with no prior effort.– Michael Berkowski
Dec 31 '18 at 13:00
add a comment |
2 Answers
2
active
oldest
votes
An insert-select statement with the distinct keyword should do the trick:
INSERT INTO tbl2
SELECT DISTINT lat, lng
FROM tbl1
answered Dec 31 '18 at 18:21
MureinikMureinik
182k22134200
Already try that
– hj2018
Dec 31 '18 at 19:25
add a comment |
I found a detour to this problem at the principle of how to eat an elephant - a piece at a time - ie. with additional two temporary table.
create temporary table t1 (
id int not null auto_increment primary key ,
lat double(10,6) not null unique
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;
create temporary table t2 (
id int not null auto_increment primary key ,
lng double(10,6) not null unique
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;
insert into t1 (lat) select distinct lat from tbl1;
insert into t2 (lng) select distinct lng from tbl1;
insert into tbl2 select null as id, lat, lng from t1 cross join t2 where t1.id=t2.id;
drop table t1;
drop table t2;
Maybe some more simple and sophisticated way could be found here?
answered Jan 1 at 9:46
hj2018hj2018
11
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53987786%2fmysql-distinct-query-result-into-another-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
An insert-select statement with the distinct keyword should do the trick:
INSERT INTO tbl2
SELECT DISTINT lat, lng
FROM tbl1
answered Dec 31 '18 at 18:21
MureinikMureinik
182k22134200
Already try that
– hj2018
Dec 31 '18 at 19:25
add a comment |
An insert-select statement with the distinct keyword should do the trick:
INSERT INTO tbl2
SELECT DISTINT lat, lng
FROM tbl1
answered Dec 31 '18 at 18:21
MureinikMureinik
182k22134200
Already try that
– hj2018
Dec 31 '18 at 19:25
add a comment |
An insert-select statement with the distinct keyword should do the trick:
INSERT INTO tbl2
SELECT DISTINT lat, lng
FROM tbl1
answered Dec 31 '18 at 18:21
MureinikMureinik
182k22134200
An insert-select statement with the distinct keyword should do the trick:
INSERT INTO tbl2
SELECT DISTINT lat, lng
FROM tbl1
answered Dec 31 '18 at 18:21
MureinikMureinik
182k22134200
answered Dec 31 '18 at 18:21
MureinikMureinik
182k22134200
answered Dec 31 '18 at 18:21
MureinikMureinik
182k22134200
answered Dec 31 '18 at 18:21
MureinikMureinik
182k22134200
182k22134200
Already try that
– hj2018
Dec 31 '18 at 19:25
add a comment |
Already try that
– hj2018
Dec 31 '18 at 19:25
Already try that
– hj2018
Dec 31 '18 at 19:25
Already try that
– hj2018
Dec 31 '18 at 19:25
add a comment |
I found a detour to this problem at the principle of how to eat an elephant - a piece at a time - ie. with additional two temporary table.
create temporary table t1 (
id int not null auto_increment primary key ,
lat double(10,6) not null unique
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;
create temporary table t2 (
id int not null auto_increment primary key ,
lng double(10,6) not null unique
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;
insert into t1 (lat) select distinct lat from tbl1;
insert into t2 (lng) select distinct lng from tbl1;
insert into tbl2 select null as id, lat, lng from t1 cross join t2 where t1.id=t2.id;
drop table t1;
drop table t2;
Maybe some more simple and sophisticated way could be found here?
answered Jan 1 at 9:46
hj2018hj2018
11
add a comment |
I found a detour to this problem at the principle of how to eat an elephant - a piece at a time - ie. with additional two temporary table.
create temporary table t1 (
id int not null auto_increment primary key ,
lat double(10,6) not null unique
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;
create temporary table t2 (
id int not null auto_increment primary key ,
lng double(10,6) not null unique
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;
insert into t1 (lat) select distinct lat from tbl1;
insert into t2 (lng) select distinct lng from tbl1;
insert into tbl2 select null as id, lat, lng from t1 cross join t2 where t1.id=t2.id;
drop table t1;
drop table t2;
Maybe some more simple and sophisticated way could be found here?
answered Jan 1 at 9:46
hj2018hj2018
11
add a comment |
I found a detour to this problem at the principle of how to eat an elephant - a piece at a time - ie. with additional two temporary table.
create temporary table t1 (
id int not null auto_increment primary key ,
lat double(10,6) not null unique
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;
create temporary table t2 (
id int not null auto_increment primary key ,
lng double(10,6) not null unique
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;
insert into t1 (lat) select distinct lat from tbl1;
insert into t2 (lng) select distinct lng from tbl1;
insert into tbl2 select null as id, lat, lng from t1 cross join t2 where t1.id=t2.id;
drop table t1;
drop table t2;
Maybe some more simple and sophisticated way could be found here?
answered Jan 1 at 9:46
hj2018hj2018
11
I found a detour to this problem at the principle of how to eat an elephant - a piece at a time - ie. with additional two temporary table.
create temporary table t1 (
id int not null auto_increment primary key ,
lat double(10,6) not null unique
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;
create temporary table t2 (
id int not null auto_increment primary key ,
lng double(10,6) not null unique
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;
insert into t1 (lat) select distinct lat from tbl1;
insert into t2 (lng) select distinct lng from tbl1;
insert into tbl2 select null as id, lat, lng from t1 cross join t2 where t1.id=t2.id;
drop table t1;
drop table t2;
Maybe some more simple and sophisticated way could be found here?
answered Jan 1 at 9:46
hj2018hj2018
11
answered Jan 1 at 9:46
hj2018hj2018
11
answered Jan 1 at 9:46
hj2018hj2018
11
answered Jan 1 at 9:46
hj2018hj2018
11
11
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53987786%2fmysql-distinct-query-result-into-another-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
What have you tried? Where are you stuck? Do you know how to use
DISTINCTin aSELECTquery? Do you know how to useSELECTin anINSERTquery?– David
Dec 31 '18 at 13:00
1
Welcome to Stack Overflow. When posting any SQL questions, it is important to provide a small sample of rows from each related table. In your case, provide a sample from
tbl1and a sample of what the result intbl2should be, given the sample fromtbl1. You should also show us any code you have already tried, as we are happy to help where stuck but few contributors are willing to provide free coding service with no prior effort.– Michael Berkowski
Dec 31 '18 at 13:00