SQL - Select the results for MAX(date) for multiple columns in single line
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
0
I would like to get the last order date from a table split into sales as purchase order
Please see below example
EXAMPLE
PRODUCT ORDER_DATE ORDER_PRICE ORDER_TYPE
0000016920 2018-05-11 135.440 Purchase Order
0000016920 2018-05-11 135.440 Sales Order
0000022670 2018-08-01 0.010 Sales Order
0000024280 2018-09-25 661.9757 Purchase Order
0000024280 2018-09-25 661.9757 Sales Order
0000025560 2018-03-14 265.5953 Sales Order
0000025560 2018-01-16 224.6733 Sales Order
0000025560 2018-03-14 265.5953 Purchase Order
0000025560 2018-03-01 224.6733 Sales Order
0000025560 2018-01-16 224.6733 Purchase Order
0000025590 2018-10-02 841.3115 Sales Order
0000025590 2018-10-02 841.3115 Purchase Order
0000025960 2018-10-02 1070.6024 Purchase Order
0000025960 2018-10-02 1070.6024 Purchase Order
0000025960 2018-10-02 1013.0212 Purchase Order
0000025960 2018-01-11 747.2314 Purchase Order
DESIRED OUTPUT
PRODUCT SALESDATE SALES_PRICE PURCHASE_DATE PPRICE
0000016920 2018-05-11 135.4400 2018-05-11 135.4400
0000022670 2018-08-01 0.0100 2018-08-01 0.0100
0000024280 2018-09-25 661.9757 2018-09-25 661.9757
0000025560 2018-03-14 265.5953 2018-03-14 265.5953
0000025560 2018-01-16 224.6733 2018-01-16 224.6733
0000025560 2018-03-01 224.6733 2018-03-01 224.6733
0000025590 2018-10-02 841.3115 2018-10-02 841.3115
0000025960 2018-03-01 747.2314 2018-03-01 747.2314
0000025960 2018-10-02 1056.2071 2018-10-02 1056.2071
0000025960 2018-10-02 1070.6024 2018-10-02 1070.6024
0000025960 2018-01-12 747.2314 2018-01-12 747.2314
sql date max
asked Nov 22 '18 at 23:36
FrikkelsFrikkels
34
add a comment |
0
I would like to get the last order date from a table split into sales as purchase order
Please see below example
EXAMPLE
PRODUCT ORDER_DATE ORDER_PRICE ORDER_TYPE
0000016920 2018-05-11 135.440 Purchase Order
0000016920 2018-05-11 135.440 Sales Order
0000022670 2018-08-01 0.010 Sales Order
0000024280 2018-09-25 661.9757 Purchase Order
0000024280 2018-09-25 661.9757 Sales Order
0000025560 2018-03-14 265.5953 Sales Order
0000025560 2018-01-16 224.6733 Sales Order
0000025560 2018-03-14 265.5953 Purchase Order
0000025560 2018-03-01 224.6733 Sales Order
0000025560 2018-01-16 224.6733 Purchase Order
0000025590 2018-10-02 841.3115 Sales Order
0000025590 2018-10-02 841.3115 Purchase Order
0000025960 2018-10-02 1070.6024 Purchase Order
0000025960 2018-10-02 1070.6024 Purchase Order
0000025960 2018-10-02 1013.0212 Purchase Order
0000025960 2018-01-11 747.2314 Purchase Order
DESIRED OUTPUT
PRODUCT SALESDATE SALES_PRICE PURCHASE_DATE PPRICE
0000016920 2018-05-11 135.4400 2018-05-11 135.4400
0000022670 2018-08-01 0.0100 2018-08-01 0.0100
0000024280 2018-09-25 661.9757 2018-09-25 661.9757
0000025560 2018-03-14 265.5953 2018-03-14 265.5953
0000025560 2018-01-16 224.6733 2018-01-16 224.6733
0000025560 2018-03-01 224.6733 2018-03-01 224.6733
0000025590 2018-10-02 841.3115 2018-10-02 841.3115
0000025960 2018-03-01 747.2314 2018-03-01 747.2314
0000025960 2018-10-02 1056.2071 2018-10-02 1056.2071
0000025960 2018-10-02 1070.6024 2018-10-02 1070.6024
0000025960 2018-01-12 747.2314 2018-01-12 747.2314
sql date max
asked Nov 22 '18 at 23:36
FrikkelsFrikkels
34
I would like to help you but can you include your query? And what rdbms you are using? Sql are just language
– dwir182
Nov 22 '18 at 23:38
add a comment |
0
0
0
1
I would like to get the last order date from a table split into sales as purchase order
Please see below example
EXAMPLE
PRODUCT ORDER_DATE ORDER_PRICE ORDER_TYPE
0000016920 2018-05-11 135.440 Purchase Order
0000016920 2018-05-11 135.440 Sales Order
0000022670 2018-08-01 0.010 Sales Order
0000024280 2018-09-25 661.9757 Purchase Order
0000024280 2018-09-25 661.9757 Sales Order
0000025560 2018-03-14 265.5953 Sales Order
0000025560 2018-01-16 224.6733 Sales Order
0000025560 2018-03-14 265.5953 Purchase Order
0000025560 2018-03-01 224.6733 Sales Order
0000025560 2018-01-16 224.6733 Purchase Order
0000025590 2018-10-02 841.3115 Sales Order
0000025590 2018-10-02 841.3115 Purchase Order
0000025960 2018-10-02 1070.6024 Purchase Order
0000025960 2018-10-02 1070.6024 Purchase Order
0000025960 2018-10-02 1013.0212 Purchase Order
0000025960 2018-01-11 747.2314 Purchase Order
DESIRED OUTPUT
PRODUCT SALESDATE SALES_PRICE PURCHASE_DATE PPRICE
0000016920 2018-05-11 135.4400 2018-05-11 135.4400
0000022670 2018-08-01 0.0100 2018-08-01 0.0100
0000024280 2018-09-25 661.9757 2018-09-25 661.9757
0000025560 2018-03-14 265.5953 2018-03-14 265.5953
0000025560 2018-01-16 224.6733 2018-01-16 224.6733
0000025560 2018-03-01 224.6733 2018-03-01 224.6733
0000025590 2018-10-02 841.3115 2018-10-02 841.3115
0000025960 2018-03-01 747.2314 2018-03-01 747.2314
0000025960 2018-10-02 1056.2071 2018-10-02 1056.2071
0000025960 2018-10-02 1070.6024 2018-10-02 1070.6024
0000025960 2018-01-12 747.2314 2018-01-12 747.2314
sql date max
asked Nov 22 '18 at 23:36
FrikkelsFrikkels
34
I would like to get the last order date from a table split into sales as purchase order
Please see below example
EXAMPLE
PRODUCT ORDER_DATE ORDER_PRICE ORDER_TYPE
0000016920 2018-05-11 135.440 Purchase Order
0000016920 2018-05-11 135.440 Sales Order
0000022670 2018-08-01 0.010 Sales Order
0000024280 2018-09-25 661.9757 Purchase Order
0000024280 2018-09-25 661.9757 Sales Order
0000025560 2018-03-14 265.5953 Sales Order
0000025560 2018-01-16 224.6733 Sales Order
0000025560 2018-03-14 265.5953 Purchase Order
0000025560 2018-03-01 224.6733 Sales Order
0000025560 2018-01-16 224.6733 Purchase Order
0000025590 2018-10-02 841.3115 Sales Order
0000025590 2018-10-02 841.3115 Purchase Order
0000025960 2018-10-02 1070.6024 Purchase Order
0000025960 2018-10-02 1070.6024 Purchase Order
0000025960 2018-10-02 1013.0212 Purchase Order
0000025960 2018-01-11 747.2314 Purchase Order
DESIRED OUTPUT
PRODUCT SALESDATE SALES_PRICE PURCHASE_DATE PPRICE
0000016920 2018-05-11 135.4400 2018-05-11 135.4400
0000022670 2018-08-01 0.0100 2018-08-01 0.0100
0000024280 2018-09-25 661.9757 2018-09-25 661.9757
0000025560 2018-03-14 265.5953 2018-03-14 265.5953
0000025560 2018-01-16 224.6733 2018-01-16 224.6733
0000025560 2018-03-01 224.6733 2018-03-01 224.6733
0000025590 2018-10-02 841.3115 2018-10-02 841.3115
0000025960 2018-03-01 747.2314 2018-03-01 747.2314
0000025960 2018-10-02 1056.2071 2018-10-02 1056.2071
0000025960 2018-10-02 1070.6024 2018-10-02 1070.6024
0000025960 2018-01-12 747.2314 2018-01-12 747.2314
sql date max
sql date max
asked Nov 22 '18 at 23:36
FrikkelsFrikkels
34
asked Nov 22 '18 at 23:36
FrikkelsFrikkels
34
asked Nov 22 '18 at 23:36
FrikkelsFrikkels
34
asked Nov 22 '18 at 23:36
FrikkelsFrikkels
34
asked Nov 22 '18 at 23:36
FrikkelsFrikkels
34
34
I would like to help you but can you include your query? And what rdbms you are using? Sql are just language
– dwir182
Nov 22 '18 at 23:38
add a comment |
I would like to help you but can you include your query? And what rdbms you are using? Sql are just language
– dwir182
Nov 22 '18 at 23:38
I would like to help you but can you include your query? And what rdbms you are using? Sql are just language
– dwir182
Nov 22 '18 at 23:38
I would like to help you but can you include your query? And what rdbms you are using? Sql are just language
– dwir182
Nov 22 '18 at 23:38
add a comment |
3 Answers
3
active
oldest
votes
0
I would just use conditional aggregation:
select product,
max(case when order_type = 'Sales Order' then order_date end) as sales_date,
max(case when order_type = 'Sales Order' then order_price end) as sales_price,
max(case when order_type = 'Purchase Order' then order_date end) as purchase_date,
max(case when order_type = 'Purchase Order' then order_price end) as purchase_price
from example
group by product;
answered Nov 23 '18 at 2:46
Gordon LinoffGordon Linoff
799k37320426
add a comment |
0
I believe you need something like that:
SELECT ISNULL(Sales.PRODUCT,Purchase.PRODUCT) AS Product
,ISNULL(Sales.ORDER_DATE ,Purchase.ORDER_DATE ) AS OrderDate
,Sales.ORDER_PRICE AS SalesPrice
,Purchase.ORDER_PRICE AS PurchasePrice
FROM
(SELECT * FROM Tbl AS T WHERE ORDER_TYPE='Sales Order') AS Sales
FULL OUTER JOIN (SELECT * FROM Tbl AS T WHERE ORDER_TYPE='Purchase Order') AS Purchase ON Sales.PRODUCT=Purchase.PRODUCT AND Sales.ORDER_DATE=Purcahse.ORDER_DATE
answered Nov 23 '18 at 0:08
Zeki GumusZeki Gumus
1,445313
add a comment |
-1
You can use something like this
SELECT (SELECT MAX(DATE1) from tbl) as date1, (SELECT MAX(DATE2) from tbl) as date2
answered Nov 22 '18 at 23:47
Mr ZachMr Zach
306113
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
});
}
});
draft saved
draft discarded
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%2f53439099%2fsql-select-the-results-for-maxdate-for-multiple-columns-in-single-line%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
I would just use conditional aggregation:
select product,
max(case when order_type = 'Sales Order' then order_date end) as sales_date,
max(case when order_type = 'Sales Order' then order_price end) as sales_price,
max(case when order_type = 'Purchase Order' then order_date end) as purchase_date,
max(case when order_type = 'Purchase Order' then order_price end) as purchase_price
from example
group by product;
answered Nov 23 '18 at 2:46
Gordon LinoffGordon Linoff
799k37320426
add a comment |
0
I would just use conditional aggregation:
select product,
max(case when order_type = 'Sales Order' then order_date end) as sales_date,
max(case when order_type = 'Sales Order' then order_price end) as sales_price,
max(case when order_type = 'Purchase Order' then order_date end) as purchase_date,
max(case when order_type = 'Purchase Order' then order_price end) as purchase_price
from example
group by product;
answered Nov 23 '18 at 2:46
Gordon LinoffGordon Linoff
799k37320426
add a comment |
0
0
0
I would just use conditional aggregation:
select product,
max(case when order_type = 'Sales Order' then order_date end) as sales_date,
max(case when order_type = 'Sales Order' then order_price end) as sales_price,
max(case when order_type = 'Purchase Order' then order_date end) as purchase_date,
max(case when order_type = 'Purchase Order' then order_price end) as purchase_price
from example
group by product;
answered Nov 23 '18 at 2:46
Gordon LinoffGordon Linoff
799k37320426
I would just use conditional aggregation:
select product,
max(case when order_type = 'Sales Order' then order_date end) as sales_date,
max(case when order_type = 'Sales Order' then order_price end) as sales_price,
max(case when order_type = 'Purchase Order' then order_date end) as purchase_date,
max(case when order_type = 'Purchase Order' then order_price end) as purchase_price
from example
group by product;
answered Nov 23 '18 at 2:46
Gordon LinoffGordon Linoff
799k37320426
answered Nov 23 '18 at 2:46
Gordon LinoffGordon Linoff
799k37320426
answered Nov 23 '18 at 2:46
Gordon LinoffGordon Linoff
799k37320426
answered Nov 23 '18 at 2:46
Gordon LinoffGordon Linoff
799k37320426
799k37320426
add a comment |
add a comment |
0
I believe you need something like that:
SELECT ISNULL(Sales.PRODUCT,Purchase.PRODUCT) AS Product
,ISNULL(Sales.ORDER_DATE ,Purchase.ORDER_DATE ) AS OrderDate
,Sales.ORDER_PRICE AS SalesPrice
,Purchase.ORDER_PRICE AS PurchasePrice
FROM
(SELECT * FROM Tbl AS T WHERE ORDER_TYPE='Sales Order') AS Sales
FULL OUTER JOIN (SELECT * FROM Tbl AS T WHERE ORDER_TYPE='Purchase Order') AS Purchase ON Sales.PRODUCT=Purchase.PRODUCT AND Sales.ORDER_DATE=Purcahse.ORDER_DATE
answered Nov 23 '18 at 0:08
Zeki GumusZeki Gumus
1,445313
add a comment |
0
I believe you need something like that:
SELECT ISNULL(Sales.PRODUCT,Purchase.PRODUCT) AS Product
,ISNULL(Sales.ORDER_DATE ,Purchase.ORDER_DATE ) AS OrderDate
,Sales.ORDER_PRICE AS SalesPrice
,Purchase.ORDER_PRICE AS PurchasePrice
FROM
(SELECT * FROM Tbl AS T WHERE ORDER_TYPE='Sales Order') AS Sales
FULL OUTER JOIN (SELECT * FROM Tbl AS T WHERE ORDER_TYPE='Purchase Order') AS Purchase ON Sales.PRODUCT=Purchase.PRODUCT AND Sales.ORDER_DATE=Purcahse.ORDER_DATE
answered Nov 23 '18 at 0:08
Zeki GumusZeki Gumus
1,445313
add a comment |
0
0
0
I believe you need something like that:
SELECT ISNULL(Sales.PRODUCT,Purchase.PRODUCT) AS Product
,ISNULL(Sales.ORDER_DATE ,Purchase.ORDER_DATE ) AS OrderDate
,Sales.ORDER_PRICE AS SalesPrice
,Purchase.ORDER_PRICE AS PurchasePrice
FROM
(SELECT * FROM Tbl AS T WHERE ORDER_TYPE='Sales Order') AS Sales
FULL OUTER JOIN (SELECT * FROM Tbl AS T WHERE ORDER_TYPE='Purchase Order') AS Purchase ON Sales.PRODUCT=Purchase.PRODUCT AND Sales.ORDER_DATE=Purcahse.ORDER_DATE
answered Nov 23 '18 at 0:08
Zeki GumusZeki Gumus
1,445313
I believe you need something like that:
SELECT ISNULL(Sales.PRODUCT,Purchase.PRODUCT) AS Product
,ISNULL(Sales.ORDER_DATE ,Purchase.ORDER_DATE ) AS OrderDate
,Sales.ORDER_PRICE AS SalesPrice
,Purchase.ORDER_PRICE AS PurchasePrice
FROM
(SELECT * FROM Tbl AS T WHERE ORDER_TYPE='Sales Order') AS Sales
FULL OUTER JOIN (SELECT * FROM Tbl AS T WHERE ORDER_TYPE='Purchase Order') AS Purchase ON Sales.PRODUCT=Purchase.PRODUCT AND Sales.ORDER_DATE=Purcahse.ORDER_DATE
answered Nov 23 '18 at 0:08
Zeki GumusZeki Gumus
1,445313
answered Nov 23 '18 at 0:08
Zeki GumusZeki Gumus
1,445313
answered Nov 23 '18 at 0:08
Zeki GumusZeki Gumus
1,445313
answered Nov 23 '18 at 0:08
Zeki GumusZeki Gumus
1,445313
1,445313
add a comment |
add a comment |
-1
You can use something like this
SELECT (SELECT MAX(DATE1) from tbl) as date1, (SELECT MAX(DATE2) from tbl) as date2
answered Nov 22 '18 at 23:47
Mr ZachMr Zach
306113
add a comment |
-1
You can use something like this
SELECT (SELECT MAX(DATE1) from tbl) as date1, (SELECT MAX(DATE2) from tbl) as date2
answered Nov 22 '18 at 23:47
Mr ZachMr Zach
306113
add a comment |
-1
-1
-1
You can use something like this
SELECT (SELECT MAX(DATE1) from tbl) as date1, (SELECT MAX(DATE2) from tbl) as date2
answered Nov 22 '18 at 23:47
Mr ZachMr Zach
306113
You can use something like this
SELECT (SELECT MAX(DATE1) from tbl) as date1, (SELECT MAX(DATE2) from tbl) as date2
answered Nov 22 '18 at 23:47
Mr ZachMr Zach
306113
answered Nov 22 '18 at 23:47
Mr ZachMr Zach
306113
answered Nov 22 '18 at 23:47
Mr ZachMr Zach
306113
answered Nov 22 '18 at 23:47
Mr ZachMr Zach
306113
306113
add a comment |
add a comment |
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
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%2f53439099%2fsql-select-the-results-for-maxdate-for-multiple-columns-in-single-line%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
I would like to help you but can you include your query? And what rdbms you are using? Sql are just language
– dwir182
Nov 22 '18 at 23:38