SQL query to get max value's data with group by
0
I have the following Table:
Date Id Count
2018-09-01 100 50
2018-09-01 101 60
2018-09-01 102 55
2018-09-02 103 40
2018-09-02 104 30
2018-09-02 105 20
2018-09-02 106 10
2018-09-03 107 30
2018-09-03 108 70
I would like to get rows having max Id for each date and its max Id's count column.
Result table:
Date Id Count
2018-09-01 102 55
2018-09-02 106 10
2018-09-03 108 70
What should be the sql query to get this result?
Thanks.
sql
sql-server tsql asked Nov 15 '18 at 14:59
developerdeveloper
2851725
add a comment |
0
I have the following Table:
Date Id Count
2018-09-01 100 50
2018-09-01 101 60
2018-09-01 102 55
2018-09-02 103 40
2018-09-02 104 30
2018-09-02 105 20
2018-09-02 106 10
2018-09-03 107 30
2018-09-03 108 70
I would like to get rows having max Id for each date and its max Id's count column.
Result table:
Date Id Count
2018-09-01 102 55
2018-09-02 106 10
2018-09-03 108 70
What should be the sql query to get this result?
Thanks.
sql
sql-server tsql asked Nov 15 '18 at 14:59
developerdeveloper
2851725
add a comment |
0
0
0
I have the following Table:
Date Id Count
2018-09-01 100 50
2018-09-01 101 60
2018-09-01 102 55
2018-09-02 103 40
2018-09-02 104 30
2018-09-02 105 20
2018-09-02 106 10
2018-09-03 107 30
2018-09-03 108 70
I would like to get rows having max Id for each date and its max Id's count column.
Result table:
Date Id Count
2018-09-01 102 55
2018-09-02 106 10
2018-09-03 108 70
What should be the sql query to get this result?
Thanks.
sql
sql-server tsql asked Nov 15 '18 at 14:59
developerdeveloper
2851725
I have the following Table:
Date Id Count
2018-09-01 100 50
2018-09-01 101 60
2018-09-01 102 55
2018-09-02 103 40
2018-09-02 104 30
2018-09-02 105 20
2018-09-02 106 10
2018-09-03 107 30
2018-09-03 108 70
I would like to get rows having max Id for each date and its max Id's count column.
Result table:
Date Id Count
2018-09-01 102 55
2018-09-02 106 10
2018-09-03 108 70
What should be the sql query to get this result?
Thanks.
sql
sql-server tsql sql
sql-server tsql asked Nov 15 '18 at 14:59
developerdeveloper
2851725
asked Nov 15 '18 at 14:59
developerdeveloper
2851725
asked Nov 15 '18 at 14:59
developerdeveloper
2851725
asked Nov 15 '18 at 14:59
developerdeveloper
2851725
asked Nov 15 '18 at 14:59
developerdeveloper
2851725
2851725
add a comment |
add a comment |
3 Answers
3
active
oldest
votes
8
Use row_number() :
select top (1) with ties t.*
from table t
order by row_number() over (partition by date order by cnt desc);
answered Nov 15 '18 at 15:00
Yogesh SharmaYogesh Sharma
32.8k51438
I love the inline of with ties but noticed it performs worse than the window function in a CTE or derived table. Have you see this?
– scsimon
Nov 15 '18 at 15:12
add a comment |
1
You don't want aggregation, you want filtering.
select t.*
from t
where t.count = (select max(t2.count) form t t2 where t2.date = t.date);
answered Nov 15 '18 at 15:00
Gordon LinoffGordon Linoff
784k35310415
add a comment |
1
I think using self join would be an alternative:
select t.*
from table t
inner join (select Date, max(t.ID) as DID from table t group by Date) t2
on t.Date = t2.Date and t.ID = t2.DID
answered Nov 15 '18 at 15:05
Eray BalkanliEray Balkanli
4,41952246
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%2f53322215%2fsql-query-to-get-max-values-data-with-group-by%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
8
Use row_number() :
select top (1) with ties t.*
from table t
order by row_number() over (partition by date order by cnt desc);
answered Nov 15 '18 at 15:00
Yogesh SharmaYogesh Sharma
32.8k51438
I love the inline of with ties but noticed it performs worse than the window function in a CTE or derived table. Have you see this?
– scsimon
Nov 15 '18 at 15:12
add a comment |
8
Use row_number() :
select top (1) with ties t.*
from table t
order by row_number() over (partition by date order by cnt desc);
answered Nov 15 '18 at 15:00
Yogesh SharmaYogesh Sharma
32.8k51438
I love the inline of with ties but noticed it performs worse than the window function in a CTE or derived table. Have you see this?
– scsimon
Nov 15 '18 at 15:12
add a comment |
8
8
8
Use row_number() :
select top (1) with ties t.*
from table t
order by row_number() over (partition by date order by cnt desc);
answered Nov 15 '18 at 15:00
Yogesh SharmaYogesh Sharma
32.8k51438
Use row_number() :
select top (1) with ties t.*
from table t
order by row_number() over (partition by date order by cnt desc);
answered Nov 15 '18 at 15:00
Yogesh SharmaYogesh Sharma
32.8k51438
answered Nov 15 '18 at 15:00
Yogesh SharmaYogesh Sharma
32.8k51438
answered Nov 15 '18 at 15:00
Yogesh SharmaYogesh Sharma
32.8k51438
answered Nov 15 '18 at 15:00
Yogesh SharmaYogesh Sharma
32.8k51438
32.8k51438
I love the inline of with ties but noticed it performs worse than the window function in a CTE or derived table. Have you see this?
– scsimon
Nov 15 '18 at 15:12
add a comment |
I love the inline of with ties but noticed it performs worse than the window function in a CTE or derived table. Have you see this?
– scsimon
Nov 15 '18 at 15:12
I love the inline of with ties but noticed it performs worse than the window function in a CTE or derived table. Have you see this?
– scsimon
Nov 15 '18 at 15:12
I love the inline of with ties but noticed it performs worse than the window function in a CTE or derived table. Have you see this?
– scsimon
Nov 15 '18 at 15:12
add a comment |
1
You don't want aggregation, you want filtering.
select t.*
from t
where t.count = (select max(t2.count) form t t2 where t2.date = t.date);
answered Nov 15 '18 at 15:00
Gordon LinoffGordon Linoff
784k35310415
add a comment |
1
You don't want aggregation, you want filtering.
select t.*
from t
where t.count = (select max(t2.count) form t t2 where t2.date = t.date);
answered Nov 15 '18 at 15:00
Gordon LinoffGordon Linoff
784k35310415
add a comment |
1
1
1
You don't want aggregation, you want filtering.
select t.*
from t
where t.count = (select max(t2.count) form t t2 where t2.date = t.date);
answered Nov 15 '18 at 15:00
Gordon LinoffGordon Linoff
784k35310415
You don't want aggregation, you want filtering.
select t.*
from t
where t.count = (select max(t2.count) form t t2 where t2.date = t.date);
answered Nov 15 '18 at 15:00
Gordon LinoffGordon Linoff
784k35310415
answered Nov 15 '18 at 15:00
Gordon LinoffGordon Linoff
784k35310415
answered Nov 15 '18 at 15:00
Gordon LinoffGordon Linoff
784k35310415
answered Nov 15 '18 at 15:00
Gordon LinoffGordon Linoff
784k35310415
784k35310415
add a comment |
add a comment |
1
I think using self join would be an alternative:
select t.*
from table t
inner join (select Date, max(t.ID) as DID from table t group by Date) t2
on t.Date = t2.Date and t.ID = t2.DID
answered Nov 15 '18 at 15:05
Eray BalkanliEray Balkanli
4,41952246
add a comment |
1
I think using self join would be an alternative:
select t.*
from table t
inner join (select Date, max(t.ID) as DID from table t group by Date) t2
on t.Date = t2.Date and t.ID = t2.DID
answered Nov 15 '18 at 15:05
Eray BalkanliEray Balkanli
4,41952246
add a comment |
1
1
1
I think using self join would be an alternative:
select t.*
from table t
inner join (select Date, max(t.ID) as DID from table t group by Date) t2
on t.Date = t2.Date and t.ID = t2.DID
answered Nov 15 '18 at 15:05
Eray BalkanliEray Balkanli
4,41952246
I think using self join would be an alternative:
select t.*
from table t
inner join (select Date, max(t.ID) as DID from table t group by Date) t2
on t.Date = t2.Date and t.ID = t2.DID
answered Nov 15 '18 at 15:05
Eray BalkanliEray Balkanli
4,41952246
edited Nov 15 '18 at 15:16
answered Nov 15 '18 at 15:05
Eray BalkanliEray Balkanli
4,41952246
answered Nov 15 '18 at 15:05
Eray BalkanliEray Balkanli
4,41952246
answered Nov 15 '18 at 15:05
Eray BalkanliEray Balkanli
4,41952246
4,41952246
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%2f53322215%2fsql-query-to-get-max-values-data-with-group-by%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
