Order by ordinal_position not producing expected output
UPDATE
I have a table with the index in order:
fsym_id, currency, x, y, z,
and the columns in order
currency, fsym_id, x, y, z
I would like to extract the names of my index in the right order.
I tried to do the following:
set session group_concat_max_len = 1000000;
select GROUP_CONCAT(CONCAT("'",COLUMN_NAME,"'"))
from INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table'
AND TABLE_SCHEMA = 'schema'
ORDER BY ORDINAL_POSITION
But this gives me the ordering of the columns. How can I modify this to return the ordering of the indexes?
mysql mysql-workbench
asked Nov 21 '18 at 20:01
ChrisChris
1189
add a comment |
UPDATE
I have a table with the index in order:
fsym_id, currency, x, y, z,
and the columns in order
currency, fsym_id, x, y, z
I would like to extract the names of my index in the right order.
I tried to do the following:
set session group_concat_max_len = 1000000;
select GROUP_CONCAT(CONCAT("'",COLUMN_NAME,"'"))
from INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table'
AND TABLE_SCHEMA = 'schema'
ORDER BY ORDINAL_POSITION
But this gives me the ordering of the columns. How can I modify this to return the ordering of the indexes?
mysql mysql-workbench
asked Nov 21 '18 at 20:01
ChrisChris
1189
Ordinal position is the order in the index, which is not the same as the order of the columns in the table.
– Barmar
Nov 21 '18 at 20:04
Please show the query you're using, some sample data, and the desired result.
– Barmar
Nov 21 '18 at 20:05
@Barmar I updated my post
– Chris
Nov 21 '18 at 20:22
@barmar 'The position of the column within the table' dev.mysql.com/doc/refman/8.0/en/columns-table.html
– P.Salmon
Nov 21 '18 at 20:26
@P.Salmon Ye well, it does not solve my problem though. I am not sure if it is even possible to extract the names of the output columns in one go. If I do.
– Chris
Nov 21 '18 at 20:32
add a comment |
UPDATE
I have a table with the index in order:
fsym_id, currency, x, y, z,
and the columns in order
currency, fsym_id, x, y, z
I would like to extract the names of my index in the right order.
I tried to do the following:
set session group_concat_max_len = 1000000;
select GROUP_CONCAT(CONCAT("'",COLUMN_NAME,"'"))
from INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table'
AND TABLE_SCHEMA = 'schema'
ORDER BY ORDINAL_POSITION
But this gives me the ordering of the columns. How can I modify this to return the ordering of the indexes?
mysql mysql-workbench
asked Nov 21 '18 at 20:01
ChrisChris
1189
UPDATE
I have a table with the index in order:
fsym_id, currency, x, y, z,
and the columns in order
currency, fsym_id, x, y, z
I would like to extract the names of my index in the right order.
I tried to do the following:
set session group_concat_max_len = 1000000;
select GROUP_CONCAT(CONCAT("'",COLUMN_NAME,"'"))
from INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table'
AND TABLE_SCHEMA = 'schema'
ORDER BY ORDINAL_POSITION
But this gives me the ordering of the columns. How can I modify this to return the ordering of the indexes?
mysql mysql-workbench
mysql mysql-workbench
asked Nov 21 '18 at 20:01
ChrisChris
1189
asked Nov 21 '18 at 20:01
ChrisChris
1189
edited Nov 21 '18 at 20:07
Chris
asked Nov 21 '18 at 20:01
ChrisChris
1189
asked Nov 21 '18 at 20:01
ChrisChris
1189
asked Nov 21 '18 at 20:01
ChrisChris
1189
1189
Ordinal position is the order in the index, which is not the same as the order of the columns in the table.
– Barmar
Nov 21 '18 at 20:04
Please show the query you're using, some sample data, and the desired result.
– Barmar
Nov 21 '18 at 20:05
@Barmar I updated my post
– Chris
Nov 21 '18 at 20:22
@barmar 'The position of the column within the table' dev.mysql.com/doc/refman/8.0/en/columns-table.html
– P.Salmon
Nov 21 '18 at 20:26
@P.Salmon Ye well, it does not solve my problem though. I am not sure if it is even possible to extract the names of the output columns in one go. If I do.
– Chris
Nov 21 '18 at 20:32
add a comment |
Ordinal position is the order in the index, which is not the same as the order of the columns in the table.
– Barmar
Nov 21 '18 at 20:04
Please show the query you're using, some sample data, and the desired result.
– Barmar
Nov 21 '18 at 20:05
@Barmar I updated my post
– Chris
Nov 21 '18 at 20:22
@barmar 'The position of the column within the table' dev.mysql.com/doc/refman/8.0/en/columns-table.html
– P.Salmon
Nov 21 '18 at 20:26
@P.Salmon Ye well, it does not solve my problem though. I am not sure if it is even possible to extract the names of the output columns in one go. If I do.
– Chris
Nov 21 '18 at 20:32
Ordinal position is the order in the index, which is not the same as the order of the columns in the table.
– Barmar
Nov 21 '18 at 20:04
Ordinal position is the order in the index, which is not the same as the order of the columns in the table.
– Barmar
Nov 21 '18 at 20:04
Please show the query you're using, some sample data, and the desired result.
– Barmar
Nov 21 '18 at 20:05
Please show the query you're using, some sample data, and the desired result.
– Barmar
Nov 21 '18 at 20:05
@Barmar I updated my post
– Chris
Nov 21 '18 at 20:22
@Barmar I updated my post
– Chris
Nov 21 '18 at 20:22
@barmar 'The position of the column within the table' dev.mysql.com/doc/refman/8.0/en/columns-table.html
– P.Salmon
Nov 21 '18 at 20:26
@barmar 'The position of the column within the table' dev.mysql.com/doc/refman/8.0/en/columns-table.html
– P.Salmon
Nov 21 '18 at 20:26
@P.Salmon Ye well, it does not solve my problem though. I am not sure if it is even possible to extract the names of the output columns in one go. If I do.
– Chris
Nov 21 '18 at 20:32
@P.Salmon Ye well, it does not solve my problem though. I am not sure if it is even possible to extract the names of the output columns in one go. If I do.
– Chris
Nov 21 '18 at 20:32
add a comment |
1 Answer
1
active
oldest
votes
If you want to order the values in a GROUP_CONCAT() you have to put the ORDER BY option in that function. Your ORDER BY clause is for ordering the rows; since you're only returning one row, it has no effect.
Also, it's not necessary to use CONCAT() inside GROUP_CONCAT(). If you give multiple values, they're automatically concatenated.
select GROUP_CONCAT("'",COLUMN_NAME,"'" ORDER BY ORDINAL_POSITION)
from INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table'
AND TABLE_SCHEMA = 'schema'
answered Nov 21 '18 at 20:37
BarmarBarmar
432k36255356
you sir, is the definition of a superior human being! FANTASTIC JOB!
– Chris
Nov 21 '18 at 20:41
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%2f53419673%2forder-by-ordinal-position-not-producing-expected-output%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
If you want to order the values in a GROUP_CONCAT() you have to put the ORDER BY option in that function. Your ORDER BY clause is for ordering the rows; since you're only returning one row, it has no effect.
Also, it's not necessary to use CONCAT() inside GROUP_CONCAT(). If you give multiple values, they're automatically concatenated.
select GROUP_CONCAT("'",COLUMN_NAME,"'" ORDER BY ORDINAL_POSITION)
from INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table'
AND TABLE_SCHEMA = 'schema'
answered Nov 21 '18 at 20:37
BarmarBarmar
432k36255356
you sir, is the definition of a superior human being! FANTASTIC JOB!
– Chris
Nov 21 '18 at 20:41
add a comment |
If you want to order the values in a GROUP_CONCAT() you have to put the ORDER BY option in that function. Your ORDER BY clause is for ordering the rows; since you're only returning one row, it has no effect.
Also, it's not necessary to use CONCAT() inside GROUP_CONCAT(). If you give multiple values, they're automatically concatenated.
select GROUP_CONCAT("'",COLUMN_NAME,"'" ORDER BY ORDINAL_POSITION)
from INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table'
AND TABLE_SCHEMA = 'schema'
answered Nov 21 '18 at 20:37
BarmarBarmar
432k36255356
you sir, is the definition of a superior human being! FANTASTIC JOB!
– Chris
Nov 21 '18 at 20:41
add a comment |
If you want to order the values in a GROUP_CONCAT() you have to put the ORDER BY option in that function. Your ORDER BY clause is for ordering the rows; since you're only returning one row, it has no effect.
Also, it's not necessary to use CONCAT() inside GROUP_CONCAT(). If you give multiple values, they're automatically concatenated.
select GROUP_CONCAT("'",COLUMN_NAME,"'" ORDER BY ORDINAL_POSITION)
from INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table'
AND TABLE_SCHEMA = 'schema'
answered Nov 21 '18 at 20:37
BarmarBarmar
432k36255356
If you want to order the values in a GROUP_CONCAT() you have to put the ORDER BY option in that function. Your ORDER BY clause is for ordering the rows; since you're only returning one row, it has no effect.
Also, it's not necessary to use CONCAT() inside GROUP_CONCAT(). If you give multiple values, they're automatically concatenated.
select GROUP_CONCAT("'",COLUMN_NAME,"'" ORDER BY ORDINAL_POSITION)
from INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table'
AND TABLE_SCHEMA = 'schema'
answered Nov 21 '18 at 20:37
BarmarBarmar
432k36255356
answered Nov 21 '18 at 20:37
BarmarBarmar
432k36255356
answered Nov 21 '18 at 20:37
BarmarBarmar
432k36255356
answered Nov 21 '18 at 20:37
BarmarBarmar
432k36255356
432k36255356
you sir, is the definition of a superior human being! FANTASTIC JOB!
– Chris
Nov 21 '18 at 20:41
add a comment |
you sir, is the definition of a superior human being! FANTASTIC JOB!
– Chris
Nov 21 '18 at 20:41
you sir, is the definition of a superior human being! FANTASTIC JOB!
– Chris
Nov 21 '18 at 20:41
you sir, is the definition of a superior human being! FANTASTIC JOB!
– Chris
Nov 21 '18 at 20:41
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%2f53419673%2forder-by-ordinal-position-not-producing-expected-output%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
Ordinal position is the order in the index, which is not the same as the order of the columns in the table.
– Barmar
Nov 21 '18 at 20:04
Please show the query you're using, some sample data, and the desired result.
– Barmar
Nov 21 '18 at 20:05
@Barmar I updated my post
– Chris
Nov 21 '18 at 20:22
@barmar 'The position of the column within the table' dev.mysql.com/doc/refman/8.0/en/columns-table.html
– P.Salmon
Nov 21 '18 at 20:26
@P.Salmon Ye well, it does not solve my problem though. I am not sure if it is even possible to extract the names of the output columns in one go. If I do.
– Chris
Nov 21 '18 at 20:32