Mixing
How to select the highest value for a given month?
+-------------------------------------------------+-----------------+---------------------+
| landing_page | all_impressions | dates |
+-------------------------------------------------+-----------------+---------------------+
| https://www.example.co.uk/url-1 | 53977 | 2018-08-19 13:59:40 |
| https://www.example.co.uk/url-1 | 610 | 2018-09-19 13:59:40 |
| https://www.example.co.uk/url-1 | 555 | 2018-10-19 13:59:40 |
| https://www.example.co.uk/url-1 | 23 | 2018-11-19 13:59:40 |
| https://www.example.co.uk/ | 1000 | 2018-06-19 13:59:40 |
| https://www.example.co.uk/ | 2 | 2018-07-19 13:59:40 |
| https://www.example.co.uk/ | 4 | 2018-08-19 13:59:40 |
| https://www.example.co.uk/ | 1563 | 2018-09-19 13:59:40 |
| https://www.example.co.uk/ | 1 | 2018-10-19 13:59:40 |
| https://www.example.co.uk/ | 9812 | 2018-11-19 13:59:40 |
+-------------------------------------------------+-----------------+---------------------+
With the above database table, I only want to select the landing_page if the impression count is the max for the current date - For example, from this, the select would return https://www.example.co.uk/ only as the current month it's all_impressions value is it's highest for November (https://www.example.co.uk/url-1 would not be selected as it's highest value was in August)
How might I do this with SQL?
index info:
mysql> show indexes from landing_pages_client_v3;
+-------------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| landing_pages_client_v3 | 0 | PRIMARY | 1 | id | A | 24279939 | NULL | NULL | | BTREE | | |
| landing_pages_client_v3 | 1 | profile_id | 1 | profile_id | A | 17 | NULL | NULL | YES | BTREE | | |
| landing_pages_client_v3 | 1 | profile_id | 2 | dates | A | 17 | NULL | NULL | | BTREE | | |
| landing_pages_client_v3 | 1 | profile_id_2 | 1 | profile_id | A | 17 | NULL | NULL | YES | BTREE | | |
| landing_pages_client_v3 | 1 | profile_id_2 | 2 | lp_id | A | 6069984 | NULL | NULL | YES | BTREE | | |
+-------------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
mysql
asked Nov 20 '18 at 16:28
AddersAdders
376118
add a comment |
+-------------------------------------------------+-----------------+---------------------+
| landing_page | all_impressions | dates |
+-------------------------------------------------+-----------------+---------------------+
| https://www.example.co.uk/url-1 | 53977 | 2018-08-19 13:59:40 |
| https://www.example.co.uk/url-1 | 610 | 2018-09-19 13:59:40 |
| https://www.example.co.uk/url-1 | 555 | 2018-10-19 13:59:40 |
| https://www.example.co.uk/url-1 | 23 | 2018-11-19 13:59:40 |
| https://www.example.co.uk/ | 1000 | 2018-06-19 13:59:40 |
| https://www.example.co.uk/ | 2 | 2018-07-19 13:59:40 |
| https://www.example.co.uk/ | 4 | 2018-08-19 13:59:40 |
| https://www.example.co.uk/ | 1563 | 2018-09-19 13:59:40 |
| https://www.example.co.uk/ | 1 | 2018-10-19 13:59:40 |
| https://www.example.co.uk/ | 9812 | 2018-11-19 13:59:40 |
+-------------------------------------------------+-----------------+---------------------+
With the above database table, I only want to select the landing_page if the impression count is the max for the current date - For example, from this, the select would return https://www.example.co.uk/ only as the current month it's all_impressions value is it's highest for November (https://www.example.co.uk/url-1 would not be selected as it's highest value was in August)
How might I do this with SQL?
index info:
mysql> show indexes from landing_pages_client_v3;
+-------------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| landing_pages_client_v3 | 0 | PRIMARY | 1 | id | A | 24279939 | NULL | NULL | | BTREE | | |
| landing_pages_client_v3 | 1 | profile_id | 1 | profile_id | A | 17 | NULL | NULL | YES | BTREE | | |
| landing_pages_client_v3 | 1 | profile_id | 2 | dates | A | 17 | NULL | NULL | | BTREE | | |
| landing_pages_client_v3 | 1 | profile_id_2 | 1 | profile_id | A | 17 | NULL | NULL | YES | BTREE | | |
| landing_pages_client_v3 | 1 | profile_id_2 | 2 | lp_id | A | 6069984 | NULL | NULL | YES | BTREE | | |
+-------------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
mysql
asked Nov 20 '18 at 16:28
AddersAdders
376118
add a comment |
+-------------------------------------------------+-----------------+---------------------+
| landing_page | all_impressions | dates |
+-------------------------------------------------+-----------------+---------------------+
| https://www.example.co.uk/url-1 | 53977 | 2018-08-19 13:59:40 |
| https://www.example.co.uk/url-1 | 610 | 2018-09-19 13:59:40 |
| https://www.example.co.uk/url-1 | 555 | 2018-10-19 13:59:40 |
| https://www.example.co.uk/url-1 | 23 | 2018-11-19 13:59:40 |
| https://www.example.co.uk/ | 1000 | 2018-06-19 13:59:40 |
| https://www.example.co.uk/ | 2 | 2018-07-19 13:59:40 |
| https://www.example.co.uk/ | 4 | 2018-08-19 13:59:40 |
| https://www.example.co.uk/ | 1563 | 2018-09-19 13:59:40 |
| https://www.example.co.uk/ | 1 | 2018-10-19 13:59:40 |
| https://www.example.co.uk/ | 9812 | 2018-11-19 13:59:40 |
+-------------------------------------------------+-----------------+---------------------+
With the above database table, I only want to select the landing_page if the impression count is the max for the current date - For example, from this, the select would return https://www.example.co.uk/ only as the current month it's all_impressions value is it's highest for November (https://www.example.co.uk/url-1 would not be selected as it's highest value was in August)
How might I do this with SQL?
index info:
mysql> show indexes from landing_pages_client_v3;
+-------------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| landing_pages_client_v3 | 0 | PRIMARY | 1 | id | A | 24279939 | NULL | NULL | | BTREE | | |
| landing_pages_client_v3 | 1 | profile_id | 1 | profile_id | A | 17 | NULL | NULL | YES | BTREE | | |
| landing_pages_client_v3 | 1 | profile_id | 2 | dates | A | 17 | NULL | NULL | | BTREE | | |
| landing_pages_client_v3 | 1 | profile_id_2 | 1 | profile_id | A | 17 | NULL | NULL | YES | BTREE | | |
| landing_pages_client_v3 | 1 | profile_id_2 | 2 | lp_id | A | 6069984 | NULL | NULL | YES | BTREE | | |
+-------------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
mysql
asked Nov 20 '18 at 16:28
AddersAdders
376118
+-------------------------------------------------+-----------------+---------------------+
| landing_page | all_impressions | dates |
+-------------------------------------------------+-----------------+---------------------+
| https://www.example.co.uk/url-1 | 53977 | 2018-08-19 13:59:40 |
| https://www.example.co.uk/url-1 | 610 | 2018-09-19 13:59:40 |
| https://www.example.co.uk/url-1 | 555 | 2018-10-19 13:59:40 |
| https://www.example.co.uk/url-1 | 23 | 2018-11-19 13:59:40 |
| https://www.example.co.uk/ | 1000 | 2018-06-19 13:59:40 |
| https://www.example.co.uk/ | 2 | 2018-07-19 13:59:40 |
| https://www.example.co.uk/ | 4 | 2018-08-19 13:59:40 |
| https://www.example.co.uk/ | 1563 | 2018-09-19 13:59:40 |
| https://www.example.co.uk/ | 1 | 2018-10-19 13:59:40 |
| https://www.example.co.uk/ | 9812 | 2018-11-19 13:59:40 |
+-------------------------------------------------+-----------------+---------------------+
With the above database table, I only want to select the landing_page if the impression count is the max for the current date - For example, from this, the select would return https://www.example.co.uk/ only as the current month it's all_impressions value is it's highest for November (https://www.example.co.uk/url-1 would not be selected as it's highest value was in August)
How might I do this with SQL?
index info:
mysql> show indexes from landing_pages_client_v3;
+-------------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| landing_pages_client_v3 | 0 | PRIMARY | 1 | id | A | 24279939 | NULL | NULL | | BTREE | | |
| landing_pages_client_v3 | 1 | profile_id | 1 | profile_id | A | 17 | NULL | NULL | YES | BTREE | | |
| landing_pages_client_v3 | 1 | profile_id | 2 | dates | A | 17 | NULL | NULL | | BTREE | | |
| landing_pages_client_v3 | 1 | profile_id_2 | 1 | profile_id | A | 17 | NULL | NULL | YES | BTREE | | |
| landing_pages_client_v3 | 1 | profile_id_2 | 2 | lp_id | A | 6069984 | NULL | NULL | YES | BTREE | | |
+-------------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
mysql
mysql
asked Nov 20 '18 at 16:28
AddersAdders
376118
asked Nov 20 '18 at 16:28
AddersAdders
376118
edited Nov 21 '18 at 8:32
Adders
asked Nov 20 '18 at 16:28
AddersAdders
376118
asked Nov 20 '18 at 16:28
AddersAdders
376118
asked Nov 20 '18 at 16:28
AddersAdders
376118
376118
add a comment |
add a comment |
3 Answers
3
active
oldest
votes
In a Derived Table, get the maximum value of all_impressions for every landing_page. Join back to the main table to get the row corresponding to maximum all_impressions value.
We will eventually consider that row only if it belongs to Current Month. For sargability, we will not use functions on the dates column. Instead, we will determine the first day of the current month and next month. We will consider those dates which fall within this range. You can check details of the datetime functions here: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html
For performance, you may also need to add the following composite index: (landing_page, all_impressions, dates). (I am not sure about which order these columns should be in. Maybe some benchmarking/trial is needed.
SELECT
t.*
FROM
your_table AS t
JOIN
(
SELECT
landing_page,
MAX(all_impressions) AS max_all_impressions
FROM your_table
GROUP BY landing_page
) AS dt
ON dt.landing_page = t.landing_page AND
dt.max_all_impressions = t.all_impressions
WHERE
t.dates >= ((LAST_DAY(CURDATE()) + INTERVAL 1 DAY) - INTERVAL 1 MONTH) AND
t.dates < (LAST_DAY(CURDATE()) + INTERVAL 1 DAY)
answered Nov 20 '18 at 16:46
Madhur BhaiyaMadhur Bhaiya
19.6k62236
thanks for this - seems to work just very slow. any way to speed it up?
– Adders
Nov 20 '18 at 21:41
Looks very efficient to me. If you have not created an index on your table's 'dates' column then you are doing a full table scan.
– Timothy Jannace
Nov 20 '18 at 23:51
@Adders please inform what all indexing is done on your columns ? Some optimization is possible based on indexing done.
– Madhur Bhaiya
Nov 21 '18 at 3:56
Across 7445480 rows it took 6 hours. On smaller data sets it's better. Updated to include index info.
– Adders
Nov 21 '18 at 8:31
@Adders please check the updated answer. You will also need to add a composite index (landing_page, all_impressions, dates). Not sure about the order of columns in it; some trial with different order maybe required.
– Madhur Bhaiya
Nov 21 '18 at 9:28
|
show 6 more comments
You can try like this way to select the landing_page url and maximum value of the all_impressions column. To do that you've to use WHERE clause to check that your dates column value is the same month and year as the CURRENT_DATE number. SEE Date and Time Functions
SELECT landing_page,MAX(all_impressions)
FROM your_table_name_goes_here
WHERE MONTH(dates) = MONTH(CURRENT_DATE())
AND YEAR(dates) = YEAR(CURRENT_DATE())
OR
SELECT landing_page
FROM your_table_name_goes_here
WHERE MONTH(dates) = MONTH(CURRENT_DATE())
AND YEAR(dates) = YEAR(CURRENT_DATE())
ORDER BY all_impressions DESC LIMIT 1
answered Nov 20 '18 at 16:34
Curious_MindCurious_Mind
15.3k32645
thanks - this doesn't work
– Adders
Nov 20 '18 at 21:47
add a comment |
In mysql. you can do like this.
SELECT landing_page,MAX(all_impressions) AS max_count
FROM your_table_name_goes_here
WHERE MONTH(dates) = MONTH(NOW()) AND YEAR(dates) = YEAR(NOW())
GROUP BY landing_page ORDER BY max_count DESC LIMIT 1
answered Nov 20 '18 at 16:49
Syed Ausaf HussainSyed Ausaf Hussain
517
thanks - this doesn't work as it returns the current month even if it's not the highest
– Adders
Nov 20 '18 at 21:46
set your required time period in where clause "MONTH(dates) = MONTH(NOW()) AND YEAR(dates) = YEAR(NOW())". here I use NOW() which set current Month. In this query first we get all the records of given time period, then group by landing_page on MAX aggregate AS max_count. then order by descending on max_count. then apply limit 1 which give only first record which is max
– Syed Ausaf Hussain
Nov 21 '18 at 11:03
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%2f53397368%2fhow-to-select-the-highest-value-for-a-given-month%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
In a Derived Table, get the maximum value of all_impressions for every landing_page. Join back to the main table to get the row corresponding to maximum all_impressions value.
We will eventually consider that row only if it belongs to Current Month. For sargability, we will not use functions on the dates column. Instead, we will determine the first day of the current month and next month. We will consider those dates which fall within this range. You can check details of the datetime functions here: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html
For performance, you may also need to add the following composite index: (landing_page, all_impressions, dates). (I am not sure about which order these columns should be in. Maybe some benchmarking/trial is needed.
SELECT
t.*
FROM
your_table AS t
JOIN
(
SELECT
landing_page,
MAX(all_impressions) AS max_all_impressions
FROM your_table
GROUP BY landing_page
) AS dt
ON dt.landing_page = t.landing_page AND
dt.max_all_impressions = t.all_impressions
WHERE
t.dates >= ((LAST_DAY(CURDATE()) + INTERVAL 1 DAY) - INTERVAL 1 MONTH) AND
t.dates < (LAST_DAY(CURDATE()) + INTERVAL 1 DAY)
answered Nov 20 '18 at 16:46
Madhur BhaiyaMadhur Bhaiya
19.6k62236
thanks for this - seems to work just very slow. any way to speed it up?
– Adders
Nov 20 '18 at 21:41
Looks very efficient to me. If you have not created an index on your table's 'dates' column then you are doing a full table scan.
– Timothy Jannace
Nov 20 '18 at 23:51
@Adders please inform what all indexing is done on your columns ? Some optimization is possible based on indexing done.
– Madhur Bhaiya
Nov 21 '18 at 3:56
Across 7445480 rows it took 6 hours. On smaller data sets it's better. Updated to include index info.
– Adders
Nov 21 '18 at 8:31
@Adders please check the updated answer. You will also need to add a composite index (landing_page, all_impressions, dates). Not sure about the order of columns in it; some trial with different order maybe required.
– Madhur Bhaiya
Nov 21 '18 at 9:28
|
show 6 more comments
In a Derived Table, get the maximum value of all_impressions for every landing_page. Join back to the main table to get the row corresponding to maximum all_impressions value.
We will eventually consider that row only if it belongs to Current Month. For sargability, we will not use functions on the dates column. Instead, we will determine the first day of the current month and next month. We will consider those dates which fall within this range. You can check details of the datetime functions here: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html
For performance, you may also need to add the following composite index: (landing_page, all_impressions, dates). (I am not sure about which order these columns should be in. Maybe some benchmarking/trial is needed.
SELECT
t.*
FROM
your_table AS t
JOIN
(
SELECT
landing_page,
MAX(all_impressions) AS max_all_impressions
FROM your_table
GROUP BY landing_page
) AS dt
ON dt.landing_page = t.landing_page AND
dt.max_all_impressions = t.all_impressions
WHERE
t.dates >= ((LAST_DAY(CURDATE()) + INTERVAL 1 DAY) - INTERVAL 1 MONTH) AND
t.dates < (LAST_DAY(CURDATE()) + INTERVAL 1 DAY)
answered Nov 20 '18 at 16:46
Madhur BhaiyaMadhur Bhaiya
19.6k62236
thanks for this - seems to work just very slow. any way to speed it up?
– Adders
Nov 20 '18 at 21:41
Looks very efficient to me. If you have not created an index on your table's 'dates' column then you are doing a full table scan.
– Timothy Jannace
Nov 20 '18 at 23:51
@Adders please inform what all indexing is done on your columns ? Some optimization is possible based on indexing done.
– Madhur Bhaiya
Nov 21 '18 at 3:56
Across 7445480 rows it took 6 hours. On smaller data sets it's better. Updated to include index info.
– Adders
Nov 21 '18 at 8:31
@Adders please check the updated answer. You will also need to add a composite index (landing_page, all_impressions, dates). Not sure about the order of columns in it; some trial with different order maybe required.
– Madhur Bhaiya
Nov 21 '18 at 9:28
|
show 6 more comments
In a Derived Table, get the maximum value of all_impressions for every landing_page. Join back to the main table to get the row corresponding to maximum all_impressions value.
We will eventually consider that row only if it belongs to Current Month. For sargability, we will not use functions on the dates column. Instead, we will determine the first day of the current month and next month. We will consider those dates which fall within this range. You can check details of the datetime functions here: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html
For performance, you may also need to add the following composite index: (landing_page, all_impressions, dates). (I am not sure about which order these columns should be in. Maybe some benchmarking/trial is needed.
SELECT
t.*
FROM
your_table AS t
JOIN
(
SELECT
landing_page,
MAX(all_impressions) AS max_all_impressions
FROM your_table
GROUP BY landing_page
) AS dt
ON dt.landing_page = t.landing_page AND
dt.max_all_impressions = t.all_impressions
WHERE
t.dates >= ((LAST_DAY(CURDATE()) + INTERVAL 1 DAY) - INTERVAL 1 MONTH) AND
t.dates < (LAST_DAY(CURDATE()) + INTERVAL 1 DAY)
answered Nov 20 '18 at 16:46
Madhur BhaiyaMadhur Bhaiya
19.6k62236
In a Derived Table, get the maximum value of all_impressions for every landing_page. Join back to the main table to get the row corresponding to maximum all_impressions value.
We will eventually consider that row only if it belongs to Current Month. For sargability, we will not use functions on the dates column. Instead, we will determine the first day of the current month and next month. We will consider those dates which fall within this range. You can check details of the datetime functions here: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html
For performance, you may also need to add the following composite index: (landing_page, all_impressions, dates). (I am not sure about which order these columns should be in. Maybe some benchmarking/trial is needed.
SELECT
t.*
FROM
your_table AS t
JOIN
(
SELECT
landing_page,
MAX(all_impressions) AS max_all_impressions
FROM your_table
GROUP BY landing_page
) AS dt
ON dt.landing_page = t.landing_page AND
dt.max_all_impressions = t.all_impressions
WHERE
t.dates >= ((LAST_DAY(CURDATE()) + INTERVAL 1 DAY) - INTERVAL 1 MONTH) AND
t.dates < (LAST_DAY(CURDATE()) + INTERVAL 1 DAY)
answered Nov 20 '18 at 16:46
Madhur BhaiyaMadhur Bhaiya
19.6k62236
edited Nov 21 '18 at 10:24
answered Nov 20 '18 at 16:46
Madhur BhaiyaMadhur Bhaiya
19.6k62236
answered Nov 20 '18 at 16:46
Madhur BhaiyaMadhur Bhaiya
19.6k62236
answered Nov 20 '18 at 16:46
Madhur BhaiyaMadhur Bhaiya
19.6k62236
19.6k62236
thanks for this - seems to work just very slow. any way to speed it up?
– Adders
Nov 20 '18 at 21:41
Looks very efficient to me. If you have not created an index on your table's 'dates' column then you are doing a full table scan.
– Timothy Jannace
Nov 20 '18 at 23:51
@Adders please inform what all indexing is done on your columns ? Some optimization is possible based on indexing done.
– Madhur Bhaiya
Nov 21 '18 at 3:56
Across 7445480 rows it took 6 hours. On smaller data sets it's better. Updated to include index info.
– Adders
Nov 21 '18 at 8:31
@Adders please check the updated answer. You will also need to add a composite index (landing_page, all_impressions, dates). Not sure about the order of columns in it; some trial with different order maybe required.
– Madhur Bhaiya
Nov 21 '18 at 9:28
|
show 6 more comments
thanks for this - seems to work just very slow. any way to speed it up?
– Adders
Nov 20 '18 at 21:41
Looks very efficient to me. If you have not created an index on your table's 'dates' column then you are doing a full table scan.
– Timothy Jannace
Nov 20 '18 at 23:51
@Adders please inform what all indexing is done on your columns ? Some optimization is possible based on indexing done.
– Madhur Bhaiya
Nov 21 '18 at 3:56
Across 7445480 rows it took 6 hours. On smaller data sets it's better. Updated to include index info.
– Adders
Nov 21 '18 at 8:31
@Adders please check the updated answer. You will also need to add a composite index (landing_page, all_impressions, dates). Not sure about the order of columns in it; some trial with different order maybe required.
– Madhur Bhaiya
Nov 21 '18 at 9:28
thanks for this - seems to work just very slow. any way to speed it up?
– Adders
Nov 20 '18 at 21:41
thanks for this - seems to work just very slow. any way to speed it up?
– Adders
Nov 20 '18 at 21:41
Looks very efficient to me. If you have not created an index on your table's 'dates' column then you are doing a full table scan.
– Timothy Jannace
Nov 20 '18 at 23:51
Looks very efficient to me. If you have not created an index on your table's 'dates' column then you are doing a full table scan.
– Timothy Jannace
Nov 20 '18 at 23:51
@Adders please inform what all indexing is done on your columns ? Some optimization is possible based on indexing done.
– Madhur Bhaiya
Nov 21 '18 at 3:56
@Adders please inform what all indexing is done on your columns ? Some optimization is possible based on indexing done.
– Madhur Bhaiya
Nov 21 '18 at 3:56
Across 7445480 rows it took 6 hours. On smaller data sets it's better. Updated to include index info.
– Adders
Nov 21 '18 at 8:31
Across 7445480 rows it took 6 hours. On smaller data sets it's better. Updated to include index info.
– Adders
Nov 21 '18 at 8:31
@Adders please check the updated answer. You will also need to add a composite index (landing_page, all_impressions, dates). Not sure about the order of columns in it; some trial with different order maybe required.
– Madhur Bhaiya
Nov 21 '18 at 9:28
@Adders please check the updated answer. You will also need to add a composite index (landing_page, all_impressions, dates). Not sure about the order of columns in it; some trial with different order maybe required.
– Madhur Bhaiya
Nov 21 '18 at 9:28
|
show 6 more comments
You can try like this way to select the landing_page url and maximum value of the all_impressions column. To do that you've to use WHERE clause to check that your dates column value is the same month and year as the CURRENT_DATE number. SEE Date and Time Functions
SELECT landing_page,MAX(all_impressions)
FROM your_table_name_goes_here
WHERE MONTH(dates) = MONTH(CURRENT_DATE())
AND YEAR(dates) = YEAR(CURRENT_DATE())
OR
SELECT landing_page
FROM your_table_name_goes_here
WHERE MONTH(dates) = MONTH(CURRENT_DATE())
AND YEAR(dates) = YEAR(CURRENT_DATE())
ORDER BY all_impressions DESC LIMIT 1
answered Nov 20 '18 at 16:34
Curious_MindCurious_Mind
15.3k32645
thanks - this doesn't work
– Adders
Nov 20 '18 at 21:47
add a comment |
You can try like this way to select the landing_page url and maximum value of the all_impressions column. To do that you've to use WHERE clause to check that your dates column value is the same month and year as the CURRENT_DATE number. SEE Date and Time Functions
SELECT landing_page,MAX(all_impressions)
FROM your_table_name_goes_here
WHERE MONTH(dates) = MONTH(CURRENT_DATE())
AND YEAR(dates) = YEAR(CURRENT_DATE())
OR
SELECT landing_page
FROM your_table_name_goes_here
WHERE MONTH(dates) = MONTH(CURRENT_DATE())
AND YEAR(dates) = YEAR(CURRENT_DATE())
ORDER BY all_impressions DESC LIMIT 1
answered Nov 20 '18 at 16:34
Curious_MindCurious_Mind
15.3k32645
thanks - this doesn't work
– Adders
Nov 20 '18 at 21:47
add a comment |
You can try like this way to select the landing_page url and maximum value of the all_impressions column. To do that you've to use WHERE clause to check that your dates column value is the same month and year as the CURRENT_DATE number. SEE Date and Time Functions
SELECT landing_page,MAX(all_impressions)
FROM your_table_name_goes_here
WHERE MONTH(dates) = MONTH(CURRENT_DATE())
AND YEAR(dates) = YEAR(CURRENT_DATE())
OR
SELECT landing_page
FROM your_table_name_goes_here
WHERE MONTH(dates) = MONTH(CURRENT_DATE())
AND YEAR(dates) = YEAR(CURRENT_DATE())
ORDER BY all_impressions DESC LIMIT 1
answered Nov 20 '18 at 16:34
Curious_MindCurious_Mind
15.3k32645
You can try like this way to select the landing_page url and maximum value of the all_impressions column. To do that you've to use WHERE clause to check that your dates column value is the same month and year as the CURRENT_DATE number. SEE Date and Time Functions
SELECT landing_page,MAX(all_impressions)
FROM your_table_name_goes_here
WHERE MONTH(dates) = MONTH(CURRENT_DATE())
AND YEAR(dates) = YEAR(CURRENT_DATE())
OR
SELECT landing_page
FROM your_table_name_goes_here
WHERE MONTH(dates) = MONTH(CURRENT_DATE())
AND YEAR(dates) = YEAR(CURRENT_DATE())
ORDER BY all_impressions DESC LIMIT 1
answered Nov 20 '18 at 16:34
Curious_MindCurious_Mind
15.3k32645
edited Nov 20 '18 at 16:40
answered Nov 20 '18 at 16:34
Curious_MindCurious_Mind
15.3k32645
answered Nov 20 '18 at 16:34
Curious_MindCurious_Mind
15.3k32645
answered Nov 20 '18 at 16:34
Curious_MindCurious_Mind
15.3k32645
15.3k32645
thanks - this doesn't work
– Adders
Nov 20 '18 at 21:47
add a comment |
thanks - this doesn't work
– Adders
Nov 20 '18 at 21:47
thanks - this doesn't work
– Adders
Nov 20 '18 at 21:47
thanks - this doesn't work
– Adders
Nov 20 '18 at 21:47
add a comment |
In mysql. you can do like this.
SELECT landing_page,MAX(all_impressions) AS max_count
FROM your_table_name_goes_here
WHERE MONTH(dates) = MONTH(NOW()) AND YEAR(dates) = YEAR(NOW())
GROUP BY landing_page ORDER BY max_count DESC LIMIT 1
answered Nov 20 '18 at 16:49
Syed Ausaf HussainSyed Ausaf Hussain
517
thanks - this doesn't work as it returns the current month even if it's not the highest
– Adders
Nov 20 '18 at 21:46
set your required time period in where clause "MONTH(dates) = MONTH(NOW()) AND YEAR(dates) = YEAR(NOW())". here I use NOW() which set current Month. In this query first we get all the records of given time period, then group by landing_page on MAX aggregate AS max_count. then order by descending on max_count. then apply limit 1 which give only first record which is max
– Syed Ausaf Hussain
Nov 21 '18 at 11:03
add a comment |
In mysql. you can do like this.
SELECT landing_page,MAX(all_impressions) AS max_count
FROM your_table_name_goes_here
WHERE MONTH(dates) = MONTH(NOW()) AND YEAR(dates) = YEAR(NOW())
GROUP BY landing_page ORDER BY max_count DESC LIMIT 1
answered Nov 20 '18 at 16:49
Syed Ausaf HussainSyed Ausaf Hussain
517
thanks - this doesn't work as it returns the current month even if it's not the highest
– Adders
Nov 20 '18 at 21:46
set your required time period in where clause "MONTH(dates) = MONTH(NOW()) AND YEAR(dates) = YEAR(NOW())". here I use NOW() which set current Month. In this query first we get all the records of given time period, then group by landing_page on MAX aggregate AS max_count. then order by descending on max_count. then apply limit 1 which give only first record which is max
– Syed Ausaf Hussain
Nov 21 '18 at 11:03
add a comment |
In mysql. you can do like this.
SELECT landing_page,MAX(all_impressions) AS max_count
FROM your_table_name_goes_here
WHERE MONTH(dates) = MONTH(NOW()) AND YEAR(dates) = YEAR(NOW())
GROUP BY landing_page ORDER BY max_count DESC LIMIT 1
answered Nov 20 '18 at 16:49
Syed Ausaf HussainSyed Ausaf Hussain
517
In mysql. you can do like this.
SELECT landing_page,MAX(all_impressions) AS max_count
FROM your_table_name_goes_here
WHERE MONTH(dates) = MONTH(NOW()) AND YEAR(dates) = YEAR(NOW())
GROUP BY landing_page ORDER BY max_count DESC LIMIT 1
answered Nov 20 '18 at 16:49
Syed Ausaf HussainSyed Ausaf Hussain
517
answered Nov 20 '18 at 16:49
Syed Ausaf HussainSyed Ausaf Hussain
517
answered Nov 20 '18 at 16:49
Syed Ausaf HussainSyed Ausaf Hussain
517
answered Nov 20 '18 at 16:49
Syed Ausaf HussainSyed Ausaf Hussain
517
517
thanks - this doesn't work as it returns the current month even if it's not the highest
– Adders
Nov 20 '18 at 21:46
set your required time period in where clause "MONTH(dates) = MONTH(NOW()) AND YEAR(dates) = YEAR(NOW())". here I use NOW() which set current Month. In this query first we get all the records of given time period, then group by landing_page on MAX aggregate AS max_count. then order by descending on max_count. then apply limit 1 which give only first record which is max
– Syed Ausaf Hussain
Nov 21 '18 at 11:03
add a comment |
thanks - this doesn't work as it returns the current month even if it's not the highest
– Adders
Nov 20 '18 at 21:46
set your required time period in where clause "MONTH(dates) = MONTH(NOW()) AND YEAR(dates) = YEAR(NOW())". here I use NOW() which set current Month. In this query first we get all the records of given time period, then group by landing_page on MAX aggregate AS max_count. then order by descending on max_count. then apply limit 1 which give only first record which is max
– Syed Ausaf Hussain
Nov 21 '18 at 11:03
thanks - this doesn't work as it returns the current month even if it's not the highest
– Adders
Nov 20 '18 at 21:46
thanks - this doesn't work as it returns the current month even if it's not the highest
– Adders
Nov 20 '18 at 21:46
set your required time period in where clause "MONTH(dates) = MONTH(NOW()) AND YEAR(dates) = YEAR(NOW())". here I use NOW() which set current Month. In this query first we get all the records of given time period, then group by landing_page on MAX aggregate AS max_count. then order by descending on max_count. then apply limit 1 which give only first record which is max
– Syed Ausaf Hussain
Nov 21 '18 at 11:03
set your required time period in where clause "MONTH(dates) = MONTH(NOW()) AND YEAR(dates) = YEAR(NOW())". here I use NOW() which set current Month. In this query first we get all the records of given time period, then group by landing_page on MAX aggregate AS max_count. then order by descending on max_count. then apply limit 1 which give only first record which is max
– Syed Ausaf Hussain
Nov 21 '18 at 11:03
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%2f53397368%2fhow-to-select-the-highest-value-for-a-given-month%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
