SQL ORDER BY Timestamp, identical values












0















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










share|improve this question


















  • 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
















0















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










share|improve this question


















  • 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














0












0








0


1






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










share|improve this question














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






share|improve this question













share|improve this question











share|improve this question




share|improve this question










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














  • 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












1 Answer
1






active

oldest

votes


















3














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?






share|improve this answer
























  • 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











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
});


}
});














draft saved

draft discarded


















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









3














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?






share|improve this answer
























  • 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
















3














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?






share|improve this answer
























  • 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














3












3








3







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?






share|improve this answer













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?







share|improve this answer












share|improve this answer



share|improve this answer










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



















  • 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




















draft saved

draft discarded




















































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.




draft saved


draft discarded














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





















































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







Popular posts from this blog

Costa Masnaga

Fotorealismo

Sidney Franklin