how to sort a “group by” clause so the value returned is the last
i have a query where i want to get the last item in a joined table, but i want to filter on that join. when i use a "group by" clause it seems to return the first value, not the last. how do i get the last instead?
for example, i have an Order table and an OrderStatus table, i want to get an Order with a specific status but only if it is the last OrderStatus...
OrderStatus table...
ID | OrderID | Status
---------------------
1 | 1 | cart
2 | 1 | pending
3 | 1 | paid
4 | 2 | cart
5 | 2 | pending
So for Status = pending I only want to return OrderID 2 and not OrderID 1. here's what I tried:
select * from `Order` where `ID` in
(select `OrderID` from `OrderStatus` where `status` like 'pending'
group by `OrderID` order by `ID` DESC)
but it returns both Orders. the order by clause is ignored, i guess it applies to the query after grouping and not before. How can I get OrderStatus = pending only if it is the last one?
mysql sql greatest-n-per-group
edited Nov 19 '18 at 2:17
Gordon Linoff
762k35294399
asked Nov 19 '18 at 2:11
mikeyc7mmikeyc7m
217
add a comment |
i have a query where i want to get the last item in a joined table, but i want to filter on that join. when i use a "group by" clause it seems to return the first value, not the last. how do i get the last instead?
for example, i have an Order table and an OrderStatus table, i want to get an Order with a specific status but only if it is the last OrderStatus...
OrderStatus table...
ID | OrderID | Status
---------------------
1 | 1 | cart
2 | 1 | pending
3 | 1 | paid
4 | 2 | cart
5 | 2 | pending
So for Status = pending I only want to return OrderID 2 and not OrderID 1. here's what I tried:
select * from `Order` where `ID` in
(select `OrderID` from `OrderStatus` where `status` like 'pending'
group by `OrderID` order by `ID` DESC)
but it returns both Orders. the order by clause is ignored, i guess it applies to the query after grouping and not before. How can I get OrderStatus = pending only if it is the last one?
mysql sql greatest-n-per-group
edited Nov 19 '18 at 2:17
Gordon Linoff
762k35294399
asked Nov 19 '18 at 2:11
mikeyc7mmikeyc7m
217
what version of MySQL are you using, it makes a difference to the answer
– Used_By_Already
Nov 19 '18 at 2:14
add a comment |
i have a query where i want to get the last item in a joined table, but i want to filter on that join. when i use a "group by" clause it seems to return the first value, not the last. how do i get the last instead?
for example, i have an Order table and an OrderStatus table, i want to get an Order with a specific status but only if it is the last OrderStatus...
OrderStatus table...
ID | OrderID | Status
---------------------
1 | 1 | cart
2 | 1 | pending
3 | 1 | paid
4 | 2 | cart
5 | 2 | pending
So for Status = pending I only want to return OrderID 2 and not OrderID 1. here's what I tried:
select * from `Order` where `ID` in
(select `OrderID` from `OrderStatus` where `status` like 'pending'
group by `OrderID` order by `ID` DESC)
but it returns both Orders. the order by clause is ignored, i guess it applies to the query after grouping and not before. How can I get OrderStatus = pending only if it is the last one?
mysql sql greatest-n-per-group
edited Nov 19 '18 at 2:17
Gordon Linoff
762k35294399
asked Nov 19 '18 at 2:11
mikeyc7mmikeyc7m
217
i have a query where i want to get the last item in a joined table, but i want to filter on that join. when i use a "group by" clause it seems to return the first value, not the last. how do i get the last instead?
for example, i have an Order table and an OrderStatus table, i want to get an Order with a specific status but only if it is the last OrderStatus...
OrderStatus table...
ID | OrderID | Status
---------------------
1 | 1 | cart
2 | 1 | pending
3 | 1 | paid
4 | 2 | cart
5 | 2 | pending
So for Status = pending I only want to return OrderID 2 and not OrderID 1. here's what I tried:
select * from `Order` where `ID` in
(select `OrderID` from `OrderStatus` where `status` like 'pending'
group by `OrderID` order by `ID` DESC)
but it returns both Orders. the order by clause is ignored, i guess it applies to the query after grouping and not before. How can I get OrderStatus = pending only if it is the last one?
mysql sql greatest-n-per-group
mysql sql greatest-n-per-group
edited Nov 19 '18 at 2:17
Gordon Linoff
762k35294399
asked Nov 19 '18 at 2:11
mikeyc7mmikeyc7m
217
edited Nov 19 '18 at 2:17
Gordon Linoff
762k35294399
asked Nov 19 '18 at 2:11
mikeyc7mmikeyc7m
217
edited Nov 19 '18 at 2:17
Gordon Linoff
762k35294399
edited Nov 19 '18 at 2:17
Gordon Linoff
762k35294399
edited Nov 19 '18 at 2:17
Gordon Linoff
762k35294399
762k35294399
asked Nov 19 '18 at 2:11
mikeyc7mmikeyc7m
217
asked Nov 19 '18 at 2:11
mikeyc7mmikeyc7m
217
asked Nov 19 '18 at 2:11
mikeyc7mmikeyc7m
217
217
what version of MySQL are you using, it makes a difference to the answer
– Used_By_Already
Nov 19 '18 at 2:14
add a comment |
what version of MySQL are you using, it makes a difference to the answer
– Used_By_Already
Nov 19 '18 at 2:14
what version of MySQL are you using, it makes a difference to the answer
– Used_By_Already
Nov 19 '18 at 2:14
what version of MySQL are you using, it makes a difference to the answer
– Used_By_Already
Nov 19 '18 at 2:14
add a comment |
2 Answers
2
active
oldest
votes
You can get the orderids whose last status is "pending" by using a having clause:
select o.*
from Orders o
where o.id in (select os.orderid
from orderstatus os
group by os.orderid
having max(os.id) = max(case when os.status = 'pending' then os.id end)
);
The comparison works by calculating the largest id and comparing it to the largest id for "pending".
answered Nov 19 '18 at 2:15
Gordon LinoffGordon Linoff
762k35294399
ooooh sweet thanks so much!
– mikeyc7m
Nov 19 '18 at 5:39
add a comment |
If i understand correctly you will get the last one with simple Limit 1 after ordering like that:
select * from Order where ID in
(select OrderID from OrderStatus where status like 'pending'
group by OrderID order by ID DESC LIMIT 1)
But im not sure tho.
answered Nov 19 '18 at 2:31
M. HelM. Hel
12
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%2f53367422%2fhow-to-sort-a-group-by-clause-so-the-value-returned-is-the-last%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
You can get the orderids whose last status is "pending" by using a having clause:
select o.*
from Orders o
where o.id in (select os.orderid
from orderstatus os
group by os.orderid
having max(os.id) = max(case when os.status = 'pending' then os.id end)
);
The comparison works by calculating the largest id and comparing it to the largest id for "pending".
answered Nov 19 '18 at 2:15
Gordon LinoffGordon Linoff
762k35294399
ooooh sweet thanks so much!
– mikeyc7m
Nov 19 '18 at 5:39
add a comment |
You can get the orderids whose last status is "pending" by using a having clause:
select o.*
from Orders o
where o.id in (select os.orderid
from orderstatus os
group by os.orderid
having max(os.id) = max(case when os.status = 'pending' then os.id end)
);
The comparison works by calculating the largest id and comparing it to the largest id for "pending".
answered Nov 19 '18 at 2:15
Gordon LinoffGordon Linoff
762k35294399
ooooh sweet thanks so much!
– mikeyc7m
Nov 19 '18 at 5:39
add a comment |
You can get the orderids whose last status is "pending" by using a having clause:
select o.*
from Orders o
where o.id in (select os.orderid
from orderstatus os
group by os.orderid
having max(os.id) = max(case when os.status = 'pending' then os.id end)
);
The comparison works by calculating the largest id and comparing it to the largest id for "pending".
answered Nov 19 '18 at 2:15
Gordon LinoffGordon Linoff
762k35294399
You can get the orderids whose last status is "pending" by using a having clause:
select o.*
from Orders o
where o.id in (select os.orderid
from orderstatus os
group by os.orderid
having max(os.id) = max(case when os.status = 'pending' then os.id end)
);
The comparison works by calculating the largest id and comparing it to the largest id for "pending".
answered Nov 19 '18 at 2:15
Gordon LinoffGordon Linoff
762k35294399
answered Nov 19 '18 at 2:15
Gordon LinoffGordon Linoff
762k35294399
answered Nov 19 '18 at 2:15
Gordon LinoffGordon Linoff
762k35294399
answered Nov 19 '18 at 2:15
Gordon LinoffGordon Linoff
762k35294399
762k35294399
ooooh sweet thanks so much!
– mikeyc7m
Nov 19 '18 at 5:39
add a comment |
ooooh sweet thanks so much!
– mikeyc7m
Nov 19 '18 at 5:39
ooooh sweet thanks so much!
– mikeyc7m
Nov 19 '18 at 5:39
ooooh sweet thanks so much!
– mikeyc7m
Nov 19 '18 at 5:39
add a comment |
If i understand correctly you will get the last one with simple Limit 1 after ordering like that:
select * from Order where ID in
(select OrderID from OrderStatus where status like 'pending'
group by OrderID order by ID DESC LIMIT 1)
But im not sure tho.
answered Nov 19 '18 at 2:31
M. HelM. Hel
12
add a comment |
If i understand correctly you will get the last one with simple Limit 1 after ordering like that:
select * from Order where ID in
(select OrderID from OrderStatus where status like 'pending'
group by OrderID order by ID DESC LIMIT 1)
But im not sure tho.
answered Nov 19 '18 at 2:31
M. HelM. Hel
12
add a comment |
If i understand correctly you will get the last one with simple Limit 1 after ordering like that:
select * from Order where ID in
(select OrderID from OrderStatus where status like 'pending'
group by OrderID order by ID DESC LIMIT 1)
But im not sure tho.
answered Nov 19 '18 at 2:31
M. HelM. Hel
12
If i understand correctly you will get the last one with simple Limit 1 after ordering like that:
select * from Order where ID in
(select OrderID from OrderStatus where status like 'pending'
group by OrderID order by ID DESC LIMIT 1)
But im not sure tho.
answered Nov 19 '18 at 2:31
M. HelM. Hel
12
answered Nov 19 '18 at 2:31
M. HelM. Hel
12
answered Nov 19 '18 at 2:31
M. HelM. Hel
12
answered Nov 19 '18 at 2:31
M. HelM. Hel
12
12
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%2f53367422%2fhow-to-sort-a-group-by-clause-so-the-value-returned-is-the-last%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
what version of MySQL are you using, it makes a difference to the answer
– Used_By_Already
Nov 19 '18 at 2:14