SQL ORDER BY Timestamp, identical values
I've got a sql data (MariaDB) with a timestamp index. Sadly there are multiple rows with the indentical timestamp. The best way would be to reject the current data and start using a unique index. Sadly that is not easily done.
When querying like that:
SELECT TIMESTAMP, DEVICE, VALUE FROM df WHERE DEVICE IN ('A', 'B') ORDER BY TIMESTAMP ASC
The order of the elements with the identical timestamp isn't the same as the order when looking at the complete data without ORDER BY TIMESTAMP ASC
. I would like to get the data in the same order as written into the SQL data. I am querying in python with pandas and my work around would be to get the complete data prepared with python but that is slower.
Can you help me? I know it should be done different in the first place but maybe there is a work-around.
Fabian
python mysql sql pandas
add a comment |
I've got a sql data (MariaDB) with a timestamp index. Sadly there are multiple rows with the indentical timestamp. The best way would be to reject the current data and start using a unique index. Sadly that is not easily done.
When querying like that:
SELECT TIMESTAMP, DEVICE, VALUE FROM df WHERE DEVICE IN ('A', 'B') ORDER BY TIMESTAMP ASC
The order of the elements with the identical timestamp isn't the same as the order when looking at the complete data without ORDER BY TIMESTAMP ASC
. I would like to get the data in the same order as written into the SQL data. I am querying in python with pandas and my work around would be to get the complete data prepared with python but that is slower.
Can you help me? I know it should be done different in the first place but maybe there is a work-around.
Fabian
python mysql sql pandas
1
If your table doesn't have a auto_increment or other column that has a guaranteed order there is no reliable answer,
– danblack
Nov 25 '18 at 22:12
As mentioned, rows in an sql have no inherent 'order' other than established by an ORDER BY clause
– Strawberry
Nov 25 '18 at 22:24
add a comment |
I've got a sql data (MariaDB) with a timestamp index. Sadly there are multiple rows with the indentical timestamp. The best way would be to reject the current data and start using a unique index. Sadly that is not easily done.
When querying like that:
SELECT TIMESTAMP, DEVICE, VALUE FROM df WHERE DEVICE IN ('A', 'B') ORDER BY TIMESTAMP ASC
The order of the elements with the identical timestamp isn't the same as the order when looking at the complete data without ORDER BY TIMESTAMP ASC
. I would like to get the data in the same order as written into the SQL data. I am querying in python with pandas and my work around would be to get the complete data prepared with python but that is slower.
Can you help me? I know it should be done different in the first place but maybe there is a work-around.
Fabian
python mysql sql pandas
I've got a sql data (MariaDB) with a timestamp index. Sadly there are multiple rows with the indentical timestamp. The best way would be to reject the current data and start using a unique index. Sadly that is not easily done.
When querying like that:
SELECT TIMESTAMP, DEVICE, VALUE FROM df WHERE DEVICE IN ('A', 'B') ORDER BY TIMESTAMP ASC
The order of the elements with the identical timestamp isn't the same as the order when looking at the complete data without ORDER BY TIMESTAMP ASC
. I would like to get the data in the same order as written into the SQL data. I am querying in python with pandas and my work around would be to get the complete data prepared with python but that is slower.
Can you help me? I know it should be done different in the first place but maybe there is a work-around.
Fabian
python mysql sql pandas
python mysql sql pandas
asked Nov 25 '18 at 22:06
Fabian ZillsFabian Zills
5916
5916
1
If your table doesn't have a auto_increment or other column that has a guaranteed order there is no reliable answer,
– danblack
Nov 25 '18 at 22:12
As mentioned, rows in an sql have no inherent 'order' other than established by an ORDER BY clause
– Strawberry
Nov 25 '18 at 22:24
add a comment |
1
If your table doesn't have a auto_increment or other column that has a guaranteed order there is no reliable answer,
– danblack
Nov 25 '18 at 22:12
As mentioned, rows in an sql have no inherent 'order' other than established by an ORDER BY clause
– Strawberry
Nov 25 '18 at 22:24
1
1
If your table doesn't have a auto_increment or other column that has a guaranteed order there is no reliable answer,
– danblack
Nov 25 '18 at 22:12
If your table doesn't have a auto_increment or other column that has a guaranteed order there is no reliable answer,
– danblack
Nov 25 '18 at 22:12
As mentioned, rows in an sql have no inherent 'order' other than established by an ORDER BY clause
– Strawberry
Nov 25 '18 at 22:24
As mentioned, rows in an sql have no inherent 'order' other than established by an ORDER BY clause
– Strawberry
Nov 25 '18 at 22:24
add a comment |
1 Answer
1
active
oldest
votes
SQL doesn't guarantee the order the data is retrieved in. You need to use another column to force data retrieval in a specific order. don't you have another column you can use in the order by?
Sadly there isn't another column I could use. But if there is no other way I will add one. If the problem still remains I may have to do some changes to the data to achieve a unique index.
– Fabian Zills
Nov 25 '18 at 22:19
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%2f53472478%2fsql-order-by-timestamp-identical-values%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
SQL doesn't guarantee the order the data is retrieved in. You need to use another column to force data retrieval in a specific order. don't you have another column you can use in the order by?
Sadly there isn't another column I could use. But if there is no other way I will add one. If the problem still remains I may have to do some changes to the data to achieve a unique index.
– Fabian Zills
Nov 25 '18 at 22:19
add a comment |
SQL doesn't guarantee the order the data is retrieved in. You need to use another column to force data retrieval in a specific order. don't you have another column you can use in the order by?
Sadly there isn't another column I could use. But if there is no other way I will add one. If the problem still remains I may have to do some changes to the data to achieve a unique index.
– Fabian Zills
Nov 25 '18 at 22:19
add a comment |
SQL doesn't guarantee the order the data is retrieved in. You need to use another column to force data retrieval in a specific order. don't you have another column you can use in the order by?
SQL doesn't guarantee the order the data is retrieved in. You need to use another column to force data retrieval in a specific order. don't you have another column you can use in the order by?
answered Nov 25 '18 at 22:16
redgenieukredgenieuk
1006
1006
Sadly there isn't another column I could use. But if there is no other way I will add one. If the problem still remains I may have to do some changes to the data to achieve a unique index.
– Fabian Zills
Nov 25 '18 at 22:19
add a comment |
Sadly there isn't another column I could use. But if there is no other way I will add one. If the problem still remains I may have to do some changes to the data to achieve a unique index.
– Fabian Zills
Nov 25 '18 at 22:19
Sadly there isn't another column I could use. But if there is no other way I will add one. If the problem still remains I may have to do some changes to the data to achieve a unique index.
– Fabian Zills
Nov 25 '18 at 22:19
Sadly there isn't another column I could use. But if there is no other way I will add one. If the problem still remains I may have to do some changes to the data to achieve a unique index.
– Fabian Zills
Nov 25 '18 at 22:19
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%2f53472478%2fsql-order-by-timestamp-identical-values%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
1
If your table doesn't have a auto_increment or other column that has a guaranteed order there is no reliable answer,
– danblack
Nov 25 '18 at 22:12
As mentioned, rows in an sql have no inherent 'order' other than established by an ORDER BY clause
– Strawberry
Nov 25 '18 at 22:24