MySQL find maximum date per month [duplicate]
1
This question already has an answer here:
Retrieving the last record in each group - MySQL
22 answers
i have a table :
date number
2018-11-01 5
2018-11-30 7
2018-10-05 3
2018-10-28 1
2018-09-01 8
2018-09-05 9
how to sort the value of the last day for each month ?
i try :
$mysqli->query("
SELECT *
FROM stats
GROUP
BY YEAR(date)
, MONTH(date) desc
ORDER
BY date desc");
and also tryed with MAX() but without success :(
i would like to have in my while rows this result :
date number
2018-11-30 7
2018-10-28 1
2018-09-05 9
so just keep the value(number) of the last day for each month.
Thanks !
mysql sql datetime group-by
edited Nov 20 '18 at 10:13
Salman A
181k66339431
asked Nov 19 '18 at 13:15
nickkonickko
13218
marked as duplicate by Strawberry mysql
Users with the mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 19 '18 at 13:21
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
add a comment |
1
This question already has an answer here:
Retrieving the last record in each group - MySQL
22 answers
i have a table :
date number
2018-11-01 5
2018-11-30 7
2018-10-05 3
2018-10-28 1
2018-09-01 8
2018-09-05 9
how to sort the value of the last day for each month ?
i try :
$mysqli->query("
SELECT *
FROM stats
GROUP
BY YEAR(date)
, MONTH(date) desc
ORDER
BY date desc");
and also tryed with MAX() but without success :(
i would like to have in my while rows this result :
date number
2018-11-30 7
2018-10-28 1
2018-09-05 9
so just keep the value(number) of the last day for each month.
Thanks !
mysql sql datetime group-by
edited Nov 20 '18 at 10:13
Salman A
181k66339431
asked Nov 19 '18 at 13:15
nickkonickko
13218
marked as duplicate by Strawberry mysql
Users with the mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 19 '18 at 13:21
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
UsingSELECT * ... GROUP BY ...is always wrong.. (My)SQL mistakes. Do you use GROUP BY correctly?
– Raymond Nijland
Nov 19 '18 at 13:22
What should happen which ties? Meaning WHERE max date is the same within the group.,
– Raymond Nijland
Nov 19 '18 at 13:24
add a comment |
1
1
1
0
This question already has an answer here:
Retrieving the last record in each group - MySQL
22 answers
i have a table :
date number
2018-11-01 5
2018-11-30 7
2018-10-05 3
2018-10-28 1
2018-09-01 8
2018-09-05 9
how to sort the value of the last day for each month ?
i try :
$mysqli->query("
SELECT *
FROM stats
GROUP
BY YEAR(date)
, MONTH(date) desc
ORDER
BY date desc");
and also tryed with MAX() but without success :(
i would like to have in my while rows this result :
date number
2018-11-30 7
2018-10-28 1
2018-09-05 9
so just keep the value(number) of the last day for each month.
Thanks !
mysql sql datetime group-by
edited Nov 20 '18 at 10:13
Salman A
181k66339431
asked Nov 19 '18 at 13:15
nickkonickko
13218
This question already has an answer here:
Retrieving the last record in each group - MySQL
22 answers
i have a table :
date number
2018-11-01 5
2018-11-30 7
2018-10-05 3
2018-10-28 1
2018-09-01 8
2018-09-05 9
how to sort the value of the last day for each month ?
i try :
$mysqli->query("
SELECT *
FROM stats
GROUP
BY YEAR(date)
, MONTH(date) desc
ORDER
BY date desc");
and also tryed with MAX() but without success :(
i would like to have in my while rows this result :
date number
2018-11-30 7
2018-10-28 1
2018-09-05 9
so just keep the value(number) of the last day for each month.
Thanks !
This question already has an answer here:
Retrieving the last record in each group - MySQL
22 answers
mysql sql datetime group-by
mysql sql datetime group-by
edited Nov 20 '18 at 10:13
Salman A
181k66339431
asked Nov 19 '18 at 13:15
nickkonickko
13218
edited Nov 20 '18 at 10:13
Salman A
181k66339431
asked Nov 19 '18 at 13:15
nickkonickko
13218
edited Nov 20 '18 at 10:13
Salman A
181k66339431
edited Nov 20 '18 at 10:13
Salman A
181k66339431
edited Nov 20 '18 at 10:13
Salman A
181k66339431
181k66339431
asked Nov 19 '18 at 13:15
nickkonickko
13218
asked Nov 19 '18 at 13:15
nickkonickko
13218
asked Nov 19 '18 at 13:15
nickkonickko
13218
13218
marked as duplicate by Strawberry mysql
Users with the mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 19 '18 at 13:21
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
marked as duplicate by Strawberry mysql
Users with the mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 19 '18 at 13:21
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
UsingSELECT * ... GROUP BY ...is always wrong.. (My)SQL mistakes. Do you use GROUP BY correctly?
– Raymond Nijland
Nov 19 '18 at 13:22
What should happen which ties? Meaning WHERE max date is the same within the group.,
– Raymond Nijland
Nov 19 '18 at 13:24
add a comment |
UsingSELECT * ... GROUP BY ...is always wrong.. (My)SQL mistakes. Do you use GROUP BY correctly?
– Raymond Nijland
Nov 19 '18 at 13:22
What should happen which ties? Meaning WHERE max date is the same within the group.,
– Raymond Nijland
Nov 19 '18 at 13:24
Using
SELECT * ... GROUP BY ... is always wrong.. (My)SQL mistakes. Do you use GROUP BY correctly?– Raymond Nijland
Nov 19 '18 at 13:22
Using
SELECT * ... GROUP BY ... is always wrong.. (My)SQL mistakes. Do you use GROUP BY correctly?– Raymond Nijland
Nov 19 '18 at 13:22
What should happen which ties? Meaning WHERE max date is the same within the group.,
– Raymond Nijland
Nov 19 '18 at 13:24
What should happen which ties? Meaning WHERE max date is the same within the group.,
– Raymond Nijland
Nov 19 '18 at 13:24
add a comment |
1 Answer
1
active
oldest
votes
2
Calculate max date for each year-month pair and join with it:
SELECT date, number
FROM stats
INNER JOIN (
SELECT MAX(date) AS maxdate
FROM stats
GROUP BY YEAR(date), MONTH(date)
) x ON stats.date = maxdate
answered Nov 19 '18 at 13:22
Salman ASalman A
181k66339431
Perfect ! I just add ORDER BY date desc at the end ;) Many thanks @Salman-A
– nickko
Nov 19 '18 at 14:47
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
2
Calculate max date for each year-month pair and join with it:
SELECT date, number
FROM stats
INNER JOIN (
SELECT MAX(date) AS maxdate
FROM stats
GROUP BY YEAR(date), MONTH(date)
) x ON stats.date = maxdate
answered Nov 19 '18 at 13:22
Salman ASalman A
181k66339431
Perfect ! I just add ORDER BY date desc at the end ;) Many thanks @Salman-A
– nickko
Nov 19 '18 at 14:47
add a comment |
2
Calculate max date for each year-month pair and join with it:
SELECT date, number
FROM stats
INNER JOIN (
SELECT MAX(date) AS maxdate
FROM stats
GROUP BY YEAR(date), MONTH(date)
) x ON stats.date = maxdate
answered Nov 19 '18 at 13:22
Salman ASalman A
181k66339431
Perfect ! I just add ORDER BY date desc at the end ;) Many thanks @Salman-A
– nickko
Nov 19 '18 at 14:47
add a comment |
2
2
2
Calculate max date for each year-month pair and join with it:
SELECT date, number
FROM stats
INNER JOIN (
SELECT MAX(date) AS maxdate
FROM stats
GROUP BY YEAR(date), MONTH(date)
) x ON stats.date = maxdate
answered Nov 19 '18 at 13:22
Salman ASalman A
181k66339431
Calculate max date for each year-month pair and join with it:
SELECT date, number
FROM stats
INNER JOIN (
SELECT MAX(date) AS maxdate
FROM stats
GROUP BY YEAR(date), MONTH(date)
) x ON stats.date = maxdate
answered Nov 19 '18 at 13:22
Salman ASalman A
181k66339431
answered Nov 19 '18 at 13:22
Salman ASalman A
181k66339431
answered Nov 19 '18 at 13:22
Salman ASalman A
181k66339431
answered Nov 19 '18 at 13:22
Salman ASalman A
181k66339431
181k66339431
Perfect ! I just add ORDER BY date desc at the end ;) Many thanks @Salman-A
– nickko
Nov 19 '18 at 14:47
add a comment |
Perfect ! I just add ORDER BY date desc at the end ;) Many thanks @Salman-A
– nickko
Nov 19 '18 at 14:47
Perfect ! I just add ORDER BY date desc at the end ;) Many thanks @Salman-A
– nickko
Nov 19 '18 at 14:47
Perfect ! I just add ORDER BY date desc at the end ;) Many thanks @Salman-A
– nickko
Nov 19 '18 at 14:47
add a comment |
Using
SELECT * ... GROUP BY ...is always wrong.. (My)SQL mistakes. Do you use GROUP BY correctly?– Raymond Nijland
Nov 19 '18 at 13:22
What should happen which ties? Meaning WHERE max date is the same within the group.,
– Raymond Nijland
Nov 19 '18 at 13:24