SQL Server: summarize results using max of other column
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I'm struggling with this one. I have the following data in the table (actually much more columns to be reduced to):
The question is how do I get to the result?
The rules are that I only want just one row per subproduct taking only the Max(changed).
Can you help me?
I tried Group by Product, subproduct but I failed miserably
HELP!
sql-server summarize edited Nov 23 '18 at 19:36
marc_s
585k13011251272
asked Nov 23 '18 at 17:25
AgustinAgustin
31
add a comment |
I'm struggling with this one. I have the following data in the table (actually much more columns to be reduced to):
The question is how do I get to the result?
The rules are that I only want just one row per subproduct taking only the Max(changed).
Can you help me?
I tried Group by Product, subproduct but I failed miserably
HELP!
sql-server summarize edited Nov 23 '18 at 19:36
marc_s
585k13011251272
asked Nov 23 '18 at 17:25
AgustinAgustin
31
add a comment |
I'm struggling with this one. I have the following data in the table (actually much more columns to be reduced to):
The question is how do I get to the result?
The rules are that I only want just one row per subproduct taking only the Max(changed).
Can you help me?
I tried Group by Product, subproduct but I failed miserably
HELP!
sql-server summarize edited Nov 23 '18 at 19:36
marc_s
585k13011251272
asked Nov 23 '18 at 17:25
AgustinAgustin
31
I'm struggling with this one. I have the following data in the table (actually much more columns to be reduced to):
The question is how do I get to the result?
The rules are that I only want just one row per subproduct taking only the Max(changed).
Can you help me?
I tried Group by Product, subproduct but I failed miserably
HELP!
sql-server summarize sql-server summarize edited Nov 23 '18 at 19:36
marc_s
585k13011251272
asked Nov 23 '18 at 17:25
AgustinAgustin
31
edited Nov 23 '18 at 19:36
marc_s
585k13011251272
asked Nov 23 '18 at 17:25
AgustinAgustin
31
edited Nov 23 '18 at 19:36
marc_s
585k13011251272
edited Nov 23 '18 at 19:36
marc_s
585k13011251272
edited Nov 23 '18 at 19:36
marc_s
585k13011251272
585k13011251272
asked Nov 23 '18 at 17:25
AgustinAgustin
31
asked Nov 23 '18 at 17:25
AgustinAgustin
31
asked Nov 23 '18 at 17:25
AgustinAgustin
31
31
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
Consider the following (change Table1 to your table name):
select a.*
from Table1 a inner join
(
select b.product, b.subproduct, max(b.changed) as mc
from Table1 b
group by b.product, b.subproduct
) c on
a.product = c.product and
a.subproduct = c.subproduct and
a.changed = c.mc
The subquery selects the Product and Subproduct with the latest Changed value and the outermost query joins the original table with the record selected by the subquery.
answered Nov 23 '18 at 18:15
Lee MacLee Mac
6,11541645
1
Thank you, thank you, thank you!
– Agustin
Nov 23 '18 at 18:57
add a comment |
SELECT * FROM
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY Product, SubProduct ORDER BY Changed DESC) rn FROM table
)
WHERE rn = 1
ROW_NUMBER() OVER(PARTITION BY Product, SubProduct ORDER BY Changed DESC) numbers the rows in descending order of Changed. Each time the Product+SubProduct combination changes, the numbering restarts from 1
Then we have an outer query that selects all the rows numbered with a 1
The reason why this works is because it's not really a group. The partition is kinda like a group but we don't lose any rows with this technique - all the rows in the input make it into the output with extra data added. In contrast, a group loses data; if you group up by product and subproduct you cannot add more columns without adding them to the grouping key, which destroys the group you're trying to create. The only other thing you can do with a column in a grouping query is run an aggregate function on it, but you can't say "get me the max Changed and i want the associated price" you can only ask for the max Changed, and the max price (or whatever) and they don't necessarily come from the same row
answered Nov 23 '18 at 17:29
Caius JardCaius Jard
12.5k21440
1
This is a great answer too, only thing I'd note is the current state of the question requires the ORDER BY to be on the "changed" column. But concept is solid.
– Eilert Hjelmeseth
Nov 25 '18 at 0:03
Thanks for the pointer! Corrections made
– Caius Jard
Nov 25 '18 at 0:06
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%2f53450809%2fsql-server-summarize-results-using-max-of-other-column%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
Consider the following (change Table1 to your table name):
select a.*
from Table1 a inner join
(
select b.product, b.subproduct, max(b.changed) as mc
from Table1 b
group by b.product, b.subproduct
) c on
a.product = c.product and
a.subproduct = c.subproduct and
a.changed = c.mc
The subquery selects the Product and Subproduct with the latest Changed value and the outermost query joins the original table with the record selected by the subquery.
answered Nov 23 '18 at 18:15
Lee MacLee Mac
6,11541645
1
Thank you, thank you, thank you!
– Agustin
Nov 23 '18 at 18:57
add a comment |
Consider the following (change Table1 to your table name):
select a.*
from Table1 a inner join
(
select b.product, b.subproduct, max(b.changed) as mc
from Table1 b
group by b.product, b.subproduct
) c on
a.product = c.product and
a.subproduct = c.subproduct and
a.changed = c.mc
The subquery selects the Product and Subproduct with the latest Changed value and the outermost query joins the original table with the record selected by the subquery.
answered Nov 23 '18 at 18:15
Lee MacLee Mac
6,11541645
1
Thank you, thank you, thank you!
– Agustin
Nov 23 '18 at 18:57
add a comment |
Consider the following (change Table1 to your table name):
select a.*
from Table1 a inner join
(
select b.product, b.subproduct, max(b.changed) as mc
from Table1 b
group by b.product, b.subproduct
) c on
a.product = c.product and
a.subproduct = c.subproduct and
a.changed = c.mc
The subquery selects the Product and Subproduct with the latest Changed value and the outermost query joins the original table with the record selected by the subquery.
answered Nov 23 '18 at 18:15
Lee MacLee Mac
6,11541645
Consider the following (change Table1 to your table name):
select a.*
from Table1 a inner join
(
select b.product, b.subproduct, max(b.changed) as mc
from Table1 b
group by b.product, b.subproduct
) c on
a.product = c.product and
a.subproduct = c.subproduct and
a.changed = c.mc
The subquery selects the Product and Subproduct with the latest Changed value and the outermost query joins the original table with the record selected by the subquery.
answered Nov 23 '18 at 18:15
Lee MacLee Mac
6,11541645
answered Nov 23 '18 at 18:15
Lee MacLee Mac
6,11541645
answered Nov 23 '18 at 18:15
Lee MacLee Mac
6,11541645
answered Nov 23 '18 at 18:15
Lee MacLee Mac
6,11541645
6,11541645
1
Thank you, thank you, thank you!
– Agustin
Nov 23 '18 at 18:57
add a comment |
1
Thank you, thank you, thank you!
– Agustin
Nov 23 '18 at 18:57
1
1
Thank you, thank you, thank you!
– Agustin
Nov 23 '18 at 18:57
Thank you, thank you, thank you!
– Agustin
Nov 23 '18 at 18:57
add a comment |
SELECT * FROM
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY Product, SubProduct ORDER BY Changed DESC) rn FROM table
)
WHERE rn = 1
ROW_NUMBER() OVER(PARTITION BY Product, SubProduct ORDER BY Changed DESC) numbers the rows in descending order of Changed. Each time the Product+SubProduct combination changes, the numbering restarts from 1
Then we have an outer query that selects all the rows numbered with a 1
The reason why this works is because it's not really a group. The partition is kinda like a group but we don't lose any rows with this technique - all the rows in the input make it into the output with extra data added. In contrast, a group loses data; if you group up by product and subproduct you cannot add more columns without adding them to the grouping key, which destroys the group you're trying to create. The only other thing you can do with a column in a grouping query is run an aggregate function on it, but you can't say "get me the max Changed and i want the associated price" you can only ask for the max Changed, and the max price (or whatever) and they don't necessarily come from the same row
answered Nov 23 '18 at 17:29
Caius JardCaius Jard
12.5k21440
1
This is a great answer too, only thing I'd note is the current state of the question requires the ORDER BY to be on the "changed" column. But concept is solid.
– Eilert Hjelmeseth
Nov 25 '18 at 0:03
Thanks for the pointer! Corrections made
– Caius Jard
Nov 25 '18 at 0:06
add a comment |
SELECT * FROM
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY Product, SubProduct ORDER BY Changed DESC) rn FROM table
)
WHERE rn = 1
ROW_NUMBER() OVER(PARTITION BY Product, SubProduct ORDER BY Changed DESC) numbers the rows in descending order of Changed. Each time the Product+SubProduct combination changes, the numbering restarts from 1
Then we have an outer query that selects all the rows numbered with a 1
The reason why this works is because it's not really a group. The partition is kinda like a group but we don't lose any rows with this technique - all the rows in the input make it into the output with extra data added. In contrast, a group loses data; if you group up by product and subproduct you cannot add more columns without adding them to the grouping key, which destroys the group you're trying to create. The only other thing you can do with a column in a grouping query is run an aggregate function on it, but you can't say "get me the max Changed and i want the associated price" you can only ask for the max Changed, and the max price (or whatever) and they don't necessarily come from the same row
answered Nov 23 '18 at 17:29
Caius JardCaius Jard
12.5k21440
1
This is a great answer too, only thing I'd note is the current state of the question requires the ORDER BY to be on the "changed" column. But concept is solid.
– Eilert Hjelmeseth
Nov 25 '18 at 0:03
Thanks for the pointer! Corrections made
– Caius Jard
Nov 25 '18 at 0:06
add a comment |
SELECT * FROM
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY Product, SubProduct ORDER BY Changed DESC) rn FROM table
)
WHERE rn = 1
ROW_NUMBER() OVER(PARTITION BY Product, SubProduct ORDER BY Changed DESC) numbers the rows in descending order of Changed. Each time the Product+SubProduct combination changes, the numbering restarts from 1
Then we have an outer query that selects all the rows numbered with a 1
The reason why this works is because it's not really a group. The partition is kinda like a group but we don't lose any rows with this technique - all the rows in the input make it into the output with extra data added. In contrast, a group loses data; if you group up by product and subproduct you cannot add more columns without adding them to the grouping key, which destroys the group you're trying to create. The only other thing you can do with a column in a grouping query is run an aggregate function on it, but you can't say "get me the max Changed and i want the associated price" you can only ask for the max Changed, and the max price (or whatever) and they don't necessarily come from the same row
answered Nov 23 '18 at 17:29
Caius JardCaius Jard
12.5k21440
SELECT * FROM
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY Product, SubProduct ORDER BY Changed DESC) rn FROM table
)
WHERE rn = 1
ROW_NUMBER() OVER(PARTITION BY Product, SubProduct ORDER BY Changed DESC) numbers the rows in descending order of Changed. Each time the Product+SubProduct combination changes, the numbering restarts from 1
Then we have an outer query that selects all the rows numbered with a 1
The reason why this works is because it's not really a group. The partition is kinda like a group but we don't lose any rows with this technique - all the rows in the input make it into the output with extra data added. In contrast, a group loses data; if you group up by product and subproduct you cannot add more columns without adding them to the grouping key, which destroys the group you're trying to create. The only other thing you can do with a column in a grouping query is run an aggregate function on it, but you can't say "get me the max Changed and i want the associated price" you can only ask for the max Changed, and the max price (or whatever) and they don't necessarily come from the same row
answered Nov 23 '18 at 17:29
Caius JardCaius Jard
12.5k21440
edited Nov 25 '18 at 0:05
answered Nov 23 '18 at 17:29
Caius JardCaius Jard
12.5k21440
answered Nov 23 '18 at 17:29
Caius JardCaius Jard
12.5k21440
answered Nov 23 '18 at 17:29
Caius JardCaius Jard
12.5k21440
12.5k21440
1
This is a great answer too, only thing I'd note is the current state of the question requires the ORDER BY to be on the "changed" column. But concept is solid.
– Eilert Hjelmeseth
Nov 25 '18 at 0:03
Thanks for the pointer! Corrections made
– Caius Jard
Nov 25 '18 at 0:06
add a comment |
1
This is a great answer too, only thing I'd note is the current state of the question requires the ORDER BY to be on the "changed" column. But concept is solid.
– Eilert Hjelmeseth
Nov 25 '18 at 0:03
Thanks for the pointer! Corrections made
– Caius Jard
Nov 25 '18 at 0:06
1
1
This is a great answer too, only thing I'd note is the current state of the question requires the ORDER BY to be on the "changed" column. But concept is solid.
– Eilert Hjelmeseth
Nov 25 '18 at 0:03
This is a great answer too, only thing I'd note is the current state of the question requires the ORDER BY to be on the "changed" column. But concept is solid.
– Eilert Hjelmeseth
Nov 25 '18 at 0:03
Thanks for the pointer! Corrections made
– Caius Jard
Nov 25 '18 at 0:06
Thanks for the pointer! Corrections made
– Caius Jard
Nov 25 '18 at 0:06
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%2f53450809%2fsql-server-summarize-results-using-max-of-other-column%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
