Mixing
How to check if dates are ascending in PostgreSQL?
How can I determine if rows from child table have ascending date or not? My sample data is next
table Person
id name
1 Bob
2 Sara
3 Mike
table Appointments
id id_apt date
1 1 2018-01-31
1 2 2018-01-31
1 3 2018-02-05
2 1 2018-01-15
2 2 2018-01-07
2 3 2018-01-09
3 1 2018-01-18
From my sample data I should get only row 2 - Sara because her appointments
2 1 2018-01-15
2 2 2018-01-07
2 3 2018-01-09
are not in ascending date order. If person have 1 appointment or no appointment at all, that person has valid appointments and I only check for persons which have invalid appointments.
Thanks in advance
postgresql
asked Nov 20 '18 at 12:01
KuKeCKuKeC
2,80031439
add a comment |
How can I determine if rows from child table have ascending date or not? My sample data is next
table Person
id name
1 Bob
2 Sara
3 Mike
table Appointments
id id_apt date
1 1 2018-01-31
1 2 2018-01-31
1 3 2018-02-05
2 1 2018-01-15
2 2 2018-01-07
2 3 2018-01-09
3 1 2018-01-18
From my sample data I should get only row 2 - Sara because her appointments
2 1 2018-01-15
2 2 2018-01-07
2 3 2018-01-09
are not in ascending date order. If person have 1 appointment or no appointment at all, that person has valid appointments and I only check for persons which have invalid appointments.
Thanks in advance
postgresql
asked Nov 20 '18 at 12:01
KuKeCKuKeC
2,80031439
add a comment |
How can I determine if rows from child table have ascending date or not? My sample data is next
table Person
id name
1 Bob
2 Sara
3 Mike
table Appointments
id id_apt date
1 1 2018-01-31
1 2 2018-01-31
1 3 2018-02-05
2 1 2018-01-15
2 2 2018-01-07
2 3 2018-01-09
3 1 2018-01-18
From my sample data I should get only row 2 - Sara because her appointments
2 1 2018-01-15
2 2 2018-01-07
2 3 2018-01-09
are not in ascending date order. If person have 1 appointment or no appointment at all, that person has valid appointments and I only check for persons which have invalid appointments.
Thanks in advance
postgresql
asked Nov 20 '18 at 12:01
KuKeCKuKeC
2,80031439
How can I determine if rows from child table have ascending date or not? My sample data is next
table Person
id name
1 Bob
2 Sara
3 Mike
table Appointments
id id_apt date
1 1 2018-01-31
1 2 2018-01-31
1 3 2018-02-05
2 1 2018-01-15
2 2 2018-01-07
2 3 2018-01-09
3 1 2018-01-18
From my sample data I should get only row 2 - Sara because her appointments
2 1 2018-01-15
2 2 2018-01-07
2 3 2018-01-09
are not in ascending date order. If person have 1 appointment or no appointment at all, that person has valid appointments and I only check for persons which have invalid appointments.
Thanks in advance
postgresql
postgresql
asked Nov 20 '18 at 12:01
KuKeCKuKeC
2,80031439
asked Nov 20 '18 at 12:01
KuKeCKuKeC
2,80031439
asked Nov 20 '18 at 12:01
KuKeCKuKeC
2,80031439
asked Nov 20 '18 at 12:01
KuKeCKuKeC
2,80031439
asked Nov 20 '18 at 12:01
KuKeCKuKeC
2,80031439
2,80031439
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Two calls of the window function row_number() show whether sort orders by id and by date differ:
select
*,
row_number() over (partition by id order by id_apt) as by_id,
row_number() over (partition by id order by date, id_apt) by_date
from appointments
id | id_apt | date | by_id | by_date
----+--------+------------+-------+---------
1 | 1 | 2018-01-31 | 1 | 1
1 | 2 | 2018-01-31 | 2 | 2
1 | 3 | 2018-02-05 | 3 | 3
2 | 2 | 2018-01-07 | 2 | 1
2 | 3 | 2018-01-09 | 3 | 2
2 | 1 | 2018-01-15 | 1 | 3
3 | 1 | 2018-01-18 | 1 | 1
(7 rows)
In the final query use the boolean aggregate bool_or():
select a.id, p.name
from (
select
id,
row_number() over (partition by id order by id_apt) as by_id,
row_number() over (partition by id order by date, id_apt) by_date
from appointments
) a
join person p using(id)
group by id, name
having bool_or(by_id <> by_date);
id | name
----+------
2 | Sara
(1 row)
answered Nov 20 '18 at 13:03
klinklin
57.6k64980
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%2f53392576%2fhow-to-check-if-dates-are-ascending-in-postgresql%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
Two calls of the window function row_number() show whether sort orders by id and by date differ:
select
*,
row_number() over (partition by id order by id_apt) as by_id,
row_number() over (partition by id order by date, id_apt) by_date
from appointments
id | id_apt | date | by_id | by_date
----+--------+------------+-------+---------
1 | 1 | 2018-01-31 | 1 | 1
1 | 2 | 2018-01-31 | 2 | 2
1 | 3 | 2018-02-05 | 3 | 3
2 | 2 | 2018-01-07 | 2 | 1
2 | 3 | 2018-01-09 | 3 | 2
2 | 1 | 2018-01-15 | 1 | 3
3 | 1 | 2018-01-18 | 1 | 1
(7 rows)
In the final query use the boolean aggregate bool_or():
select a.id, p.name
from (
select
id,
row_number() over (partition by id order by id_apt) as by_id,
row_number() over (partition by id order by date, id_apt) by_date
from appointments
) a
join person p using(id)
group by id, name
having bool_or(by_id <> by_date);
id | name
----+------
2 | Sara
(1 row)
answered Nov 20 '18 at 13:03
klinklin
57.6k64980
add a comment |
Two calls of the window function row_number() show whether sort orders by id and by date differ:
select
*,
row_number() over (partition by id order by id_apt) as by_id,
row_number() over (partition by id order by date, id_apt) by_date
from appointments
id | id_apt | date | by_id | by_date
----+--------+------------+-------+---------
1 | 1 | 2018-01-31 | 1 | 1
1 | 2 | 2018-01-31 | 2 | 2
1 | 3 | 2018-02-05 | 3 | 3
2 | 2 | 2018-01-07 | 2 | 1
2 | 3 | 2018-01-09 | 3 | 2
2 | 1 | 2018-01-15 | 1 | 3
3 | 1 | 2018-01-18 | 1 | 1
(7 rows)
In the final query use the boolean aggregate bool_or():
select a.id, p.name
from (
select
id,
row_number() over (partition by id order by id_apt) as by_id,
row_number() over (partition by id order by date, id_apt) by_date
from appointments
) a
join person p using(id)
group by id, name
having bool_or(by_id <> by_date);
id | name
----+------
2 | Sara
(1 row)
answered Nov 20 '18 at 13:03
klinklin
57.6k64980
add a comment |
Two calls of the window function row_number() show whether sort orders by id and by date differ:
select
*,
row_number() over (partition by id order by id_apt) as by_id,
row_number() over (partition by id order by date, id_apt) by_date
from appointments
id | id_apt | date | by_id | by_date
----+--------+------------+-------+---------
1 | 1 | 2018-01-31 | 1 | 1
1 | 2 | 2018-01-31 | 2 | 2
1 | 3 | 2018-02-05 | 3 | 3
2 | 2 | 2018-01-07 | 2 | 1
2 | 3 | 2018-01-09 | 3 | 2
2 | 1 | 2018-01-15 | 1 | 3
3 | 1 | 2018-01-18 | 1 | 1
(7 rows)
In the final query use the boolean aggregate bool_or():
select a.id, p.name
from (
select
id,
row_number() over (partition by id order by id_apt) as by_id,
row_number() over (partition by id order by date, id_apt) by_date
from appointments
) a
join person p using(id)
group by id, name
having bool_or(by_id <> by_date);
id | name
----+------
2 | Sara
(1 row)
answered Nov 20 '18 at 13:03
klinklin
57.6k64980
Two calls of the window function row_number() show whether sort orders by id and by date differ:
select
*,
row_number() over (partition by id order by id_apt) as by_id,
row_number() over (partition by id order by date, id_apt) by_date
from appointments
id | id_apt | date | by_id | by_date
----+--------+------------+-------+---------
1 | 1 | 2018-01-31 | 1 | 1
1 | 2 | 2018-01-31 | 2 | 2
1 | 3 | 2018-02-05 | 3 | 3
2 | 2 | 2018-01-07 | 2 | 1
2 | 3 | 2018-01-09 | 3 | 2
2 | 1 | 2018-01-15 | 1 | 3
3 | 1 | 2018-01-18 | 1 | 1
(7 rows)
In the final query use the boolean aggregate bool_or():
select a.id, p.name
from (
select
id,
row_number() over (partition by id order by id_apt) as by_id,
row_number() over (partition by id order by date, id_apt) by_date
from appointments
) a
join person p using(id)
group by id, name
having bool_or(by_id <> by_date);
id | name
----+------
2 | Sara
(1 row)
answered Nov 20 '18 at 13:03
klinklin
57.6k64980
answered Nov 20 '18 at 13:03
klinklin
57.6k64980
answered Nov 20 '18 at 13:03
klinklin
57.6k64980
answered Nov 20 '18 at 13:03
klinklin
57.6k64980
57.6k64980
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%2f53392576%2fhow-to-check-if-dates-are-ascending-in-postgresql%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
