MYSQL query that only give the first occurrence of each group that can be saved as a View
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I'm struggling to correctly query what appears to be simple but the only solution I was able to come up with I can't save as a view since it contained a variable. What I am trying to do is select only the first occurrence of each StartDate/idcustomerL combination. When I try to use Group by StartDate, idcustomerL it doesn't give me the first result (I tried ordering it different ways with no luck either).
Below is the table I am working with.
StartDate = Each StartDateC and StartDateR for each customer
total = day differential of StartDateC + StartDateR from StartDate (I did this to create the order needed to determine what StartDate to use)
Table
+------------+------------+------------+------------+-------+
| StartDate | idcustomer | StartDateC | StartDateR | total |
+------------+------------+------------+------------+-------+
| 2018-11-06 | 20 | 2018-11-06 | 2018-10-01 | 36 |
+------------+------------+------------+------------+-------+
| 2018-11-06 | 20 | 2018-11-06 | 2018-01-01 | 309 |
+------------+------------+------------+------------+-------+
| 2018-10-01 | 20 | 2018-10-01 | 2018-10-01 | 0 |
+------------+------------+------------+------------+-------+
| 2018-10-01 | 20 | 2018-08-30 | 2018-10-01 | 32 |
+------------+------------+------------+------------+-------+
| 2018-10-01 | 20 | 2018-10-01 | 2018-01-01 | 273 |
+------------+------------+------------+------------+-------+
| 2018-08-30 | 20 | 2018-08-30 | 2018-01-01 | 241 |
+------------+------------+------------+------------+-------+
| 2018-10-01 | 62 | 2018-10-01 | 2018-10-01 | 0 |
+------------+------------+------------+------------+-------+
| 2018-10-01 | 62 | 2018-08-30 | 2018-10-01 | 32 |
+------------+------------+------------+------------+-------+
| 2018-10-01 | 62 | 2018-10-01 | 2018-01-01 | 273 |
+------------+------------+------------+------------+-------+
| 2018-08-30 | 62 | 2018-08-30 | 2018-01-01 | 241 |
+------------+------------+------------+------------+-------+
This is the result I need to get and I need to be able to save the query as a view.
+------------+------------+------------+------------+-------+
| StartDate | idcustomer | StartDateC | StartDateR | total |
+------------+------------+------------+------------+-------+
| 2018-11-06 | 20 | 2018-11-06 | 2018-10-01 | 36 |
+------------+------------+------------+------------+-------+
| 2018-10-01 | 20 | 2018-10-01 | 2018-10-01 | 0 |
+------------+------------+------------+------------+-------+
| 2018-08-30 | 20 | 2018-08-30 | 2018-01-01 | 241 |
+------------+------------+------------+------------+-------+
| 2018-10-01 | 62 | 2018-10-01 | 2018-10-01 | 0 |
+------------+------------+------------+------------+-------+
| 2018-08-30 | 62 | 2018-08-30 | 2018-01-01 | 241 |
+------------+------------+------------+------------+-------+
I can get the result I am looking for with the following but I can not save it as a view because of the @count so I need to find another solution.
Select (@count := @count + 1) AS rowNumber, StartDate, idcustomerL, StartDateC, StartDateR, Test as totals
from test
CROSS JOIN (SELECT @count := 0) AS dummy
Group by StartDate,idcustomerL order by idcustomerL, StartDate desc, totals
mysql
asked Nov 22 '18 at 16:37
Steve ScanSteve Scan
346
add a comment |
I'm struggling to correctly query what appears to be simple but the only solution I was able to come up with I can't save as a view since it contained a variable. What I am trying to do is select only the first occurrence of each StartDate/idcustomerL combination. When I try to use Group by StartDate, idcustomerL it doesn't give me the first result (I tried ordering it different ways with no luck either).
Below is the table I am working with.
StartDate = Each StartDateC and StartDateR for each customer
total = day differential of StartDateC + StartDateR from StartDate (I did this to create the order needed to determine what StartDate to use)
Table
+------------+------------+------------+------------+-------+
| StartDate | idcustomer | StartDateC | StartDateR | total |
+------------+------------+------------+------------+-------+
| 2018-11-06 | 20 | 2018-11-06 | 2018-10-01 | 36 |
+------------+------------+------------+------------+-------+
| 2018-11-06 | 20 | 2018-11-06 | 2018-01-01 | 309 |
+------------+------------+------------+------------+-------+
| 2018-10-01 | 20 | 2018-10-01 | 2018-10-01 | 0 |
+------------+------------+------------+------------+-------+
| 2018-10-01 | 20 | 2018-08-30 | 2018-10-01 | 32 |
+------------+------------+------------+------------+-------+
| 2018-10-01 | 20 | 2018-10-01 | 2018-01-01 | 273 |
+------------+------------+------------+------------+-------+
| 2018-08-30 | 20 | 2018-08-30 | 2018-01-01 | 241 |
+------------+------------+------------+------------+-------+
| 2018-10-01 | 62 | 2018-10-01 | 2018-10-01 | 0 |
+------------+------------+------------+------------+-------+
| 2018-10-01 | 62 | 2018-08-30 | 2018-10-01 | 32 |
+------------+------------+------------+------------+-------+
| 2018-10-01 | 62 | 2018-10-01 | 2018-01-01 | 273 |
+------------+------------+------------+------------+-------+
| 2018-08-30 | 62 | 2018-08-30 | 2018-01-01 | 241 |
+------------+------------+------------+------------+-------+
This is the result I need to get and I need to be able to save the query as a view.
+------------+------------+------------+------------+-------+
| StartDate | idcustomer | StartDateC | StartDateR | total |
+------------+------------+------------+------------+-------+
| 2018-11-06 | 20 | 2018-11-06 | 2018-10-01 | 36 |
+------------+------------+------------+------------+-------+
| 2018-10-01 | 20 | 2018-10-01 | 2018-10-01 | 0 |
+------------+------------+------------+------------+-------+
| 2018-08-30 | 20 | 2018-08-30 | 2018-01-01 | 241 |
+------------+------------+------------+------------+-------+
| 2018-10-01 | 62 | 2018-10-01 | 2018-10-01 | 0 |
+------------+------------+------------+------------+-------+
| 2018-08-30 | 62 | 2018-08-30 | 2018-01-01 | 241 |
+------------+------------+------------+------------+-------+
I can get the result I am looking for with the following but I can not save it as a view because of the @count so I need to find another solution.
Select (@count := @count + 1) AS rowNumber, StartDate, idcustomerL, StartDateC, StartDateR, Test as totals
from test
CROSS JOIN (SELECT @count := 0) AS dummy
Group by StartDate,idcustomerL order by idcustomerL, StartDate desc, totals
mysql
asked Nov 22 '18 at 16:37
Steve ScanSteve Scan
346
Please edit the question and change table to proper formatting. You can use tablesgenerator.com/text_tables Although it would be best, if you can setup a db-fiddle.com on what you are trying to do.
– Chowkidar Madhur Bhaiya
Nov 22 '18 at 17:42
add a comment |
I'm struggling to correctly query what appears to be simple but the only solution I was able to come up with I can't save as a view since it contained a variable. What I am trying to do is select only the first occurrence of each StartDate/idcustomerL combination. When I try to use Group by StartDate, idcustomerL it doesn't give me the first result (I tried ordering it different ways with no luck either).
Below is the table I am working with.
StartDate = Each StartDateC and StartDateR for each customer
total = day differential of StartDateC + StartDateR from StartDate (I did this to create the order needed to determine what StartDate to use)
Table
+------------+------------+------------+------------+-------+
| StartDate | idcustomer | StartDateC | StartDateR | total |
+------------+------------+------------+------------+-------+
| 2018-11-06 | 20 | 2018-11-06 | 2018-10-01 | 36 |
+------------+------------+------------+------------+-------+
| 2018-11-06 | 20 | 2018-11-06 | 2018-01-01 | 309 |
+------------+------------+------------+------------+-------+
| 2018-10-01 | 20 | 2018-10-01 | 2018-10-01 | 0 |
+------------+------------+------------+------------+-------+
| 2018-10-01 | 20 | 2018-08-30 | 2018-10-01 | 32 |
+------------+------------+------------+------------+-------+
| 2018-10-01 | 20 | 2018-10-01 | 2018-01-01 | 273 |
+------------+------------+------------+------------+-------+
| 2018-08-30 | 20 | 2018-08-30 | 2018-01-01 | 241 |
+------------+------------+------------+------------+-------+
| 2018-10-01 | 62 | 2018-10-01 | 2018-10-01 | 0 |
+------------+------------+------------+------------+-------+
| 2018-10-01 | 62 | 2018-08-30 | 2018-10-01 | 32 |
+------------+------------+------------+------------+-------+
| 2018-10-01 | 62 | 2018-10-01 | 2018-01-01 | 273 |
+------------+------------+------------+------------+-------+
| 2018-08-30 | 62 | 2018-08-30 | 2018-01-01 | 241 |
+------------+------------+------------+------------+-------+
This is the result I need to get and I need to be able to save the query as a view.
+------------+------------+------------+------------+-------+
| StartDate | idcustomer | StartDateC | StartDateR | total |
+------------+------------+------------+------------+-------+
| 2018-11-06 | 20 | 2018-11-06 | 2018-10-01 | 36 |
+------------+------------+------------+------------+-------+
| 2018-10-01 | 20 | 2018-10-01 | 2018-10-01 | 0 |
+------------+------------+------------+------------+-------+
| 2018-08-30 | 20 | 2018-08-30 | 2018-01-01 | 241 |
+------------+------------+------------+------------+-------+
| 2018-10-01 | 62 | 2018-10-01 | 2018-10-01 | 0 |
+------------+------------+------------+------------+-------+
| 2018-08-30 | 62 | 2018-08-30 | 2018-01-01 | 241 |
+------------+------------+------------+------------+-------+
I can get the result I am looking for with the following but I can not save it as a view because of the @count so I need to find another solution.
Select (@count := @count + 1) AS rowNumber, StartDate, idcustomerL, StartDateC, StartDateR, Test as totals
from test
CROSS JOIN (SELECT @count := 0) AS dummy
Group by StartDate,idcustomerL order by idcustomerL, StartDate desc, totals
mysql
asked Nov 22 '18 at 16:37
Steve ScanSteve Scan
346
I'm struggling to correctly query what appears to be simple but the only solution I was able to come up with I can't save as a view since it contained a variable. What I am trying to do is select only the first occurrence of each StartDate/idcustomerL combination. When I try to use Group by StartDate, idcustomerL it doesn't give me the first result (I tried ordering it different ways with no luck either).
Below is the table I am working with.
StartDate = Each StartDateC and StartDateR for each customer
total = day differential of StartDateC + StartDateR from StartDate (I did this to create the order needed to determine what StartDate to use)
Table
+------------+------------+------------+------------+-------+
| StartDate | idcustomer | StartDateC | StartDateR | total |
+------------+------------+------------+------------+-------+
| 2018-11-06 | 20 | 2018-11-06 | 2018-10-01 | 36 |
+------------+------------+------------+------------+-------+
| 2018-11-06 | 20 | 2018-11-06 | 2018-01-01 | 309 |
+------------+------------+------------+------------+-------+
| 2018-10-01 | 20 | 2018-10-01 | 2018-10-01 | 0 |
+------------+------------+------------+------------+-------+
| 2018-10-01 | 20 | 2018-08-30 | 2018-10-01 | 32 |
+------------+------------+------------+------------+-------+
| 2018-10-01 | 20 | 2018-10-01 | 2018-01-01 | 273 |
+------------+------------+------------+------------+-------+
| 2018-08-30 | 20 | 2018-08-30 | 2018-01-01 | 241 |
+------------+------------+------------+------------+-------+
| 2018-10-01 | 62 | 2018-10-01 | 2018-10-01 | 0 |
+------------+------------+------------+------------+-------+
| 2018-10-01 | 62 | 2018-08-30 | 2018-10-01 | 32 |
+------------+------------+------------+------------+-------+
| 2018-10-01 | 62 | 2018-10-01 | 2018-01-01 | 273 |
+------------+------------+------------+------------+-------+
| 2018-08-30 | 62 | 2018-08-30 | 2018-01-01 | 241 |
+------------+------------+------------+------------+-------+
This is the result I need to get and I need to be able to save the query as a view.
+------------+------------+------------+------------+-------+
| StartDate | idcustomer | StartDateC | StartDateR | total |
+------------+------------+------------+------------+-------+
| 2018-11-06 | 20 | 2018-11-06 | 2018-10-01 | 36 |
+------------+------------+------------+------------+-------+
| 2018-10-01 | 20 | 2018-10-01 | 2018-10-01 | 0 |
+------------+------------+------------+------------+-------+
| 2018-08-30 | 20 | 2018-08-30 | 2018-01-01 | 241 |
+------------+------------+------------+------------+-------+
| 2018-10-01 | 62 | 2018-10-01 | 2018-10-01 | 0 |
+------------+------------+------------+------------+-------+
| 2018-08-30 | 62 | 2018-08-30 | 2018-01-01 | 241 |
+------------+------------+------------+------------+-------+
I can get the result I am looking for with the following but I can not save it as a view because of the @count so I need to find another solution.
Select (@count := @count + 1) AS rowNumber, StartDate, idcustomerL, StartDateC, StartDateR, Test as totals
from test
CROSS JOIN (SELECT @count := 0) AS dummy
Group by StartDate,idcustomerL order by idcustomerL, StartDate desc, totals
mysql
mysql
asked Nov 22 '18 at 16:37
Steve ScanSteve Scan
346
asked Nov 22 '18 at 16:37
Steve ScanSteve Scan
346
edited Nov 23 '18 at 2:11
Steve Scan
asked Nov 22 '18 at 16:37
Steve ScanSteve Scan
346
asked Nov 22 '18 at 16:37
Steve ScanSteve Scan
346
asked Nov 22 '18 at 16:37
Steve ScanSteve Scan
346
346
Please edit the question and change table to proper formatting. You can use tablesgenerator.com/text_tables Although it would be best, if you can setup a db-fiddle.com on what you are trying to do.
– Chowkidar Madhur Bhaiya
Nov 22 '18 at 17:42
add a comment |
Please edit the question and change table to proper formatting. You can use tablesgenerator.com/text_tables Although it would be best, if you can setup a db-fiddle.com on what you are trying to do.
– Chowkidar Madhur Bhaiya
Nov 22 '18 at 17:42
Please edit the question and change table to proper formatting. You can use tablesgenerator.com/text_tables Although it would be best, if you can setup a db-fiddle.com on what you are trying to do.
– Chowkidar Madhur Bhaiya
Nov 22 '18 at 17:42
Please edit the question and change table to proper formatting. You can use tablesgenerator.com/text_tables Although it would be best, if you can setup a db-fiddle.com on what you are trying to do.
– Chowkidar Madhur Bhaiya
Nov 22 '18 at 17:42
add a comment |
1 Answer
1
active
oldest
votes
It seems that the "first" row within a group of idcustomer and StartDate is defined as the one having the lowest total value.
We can determine the lowest total value for the specified group in a Derived Table. We will then JOIN this result-set back to the main table using the lowest total value, idcustomer and StartDate. This will give us the complete row against the lowest total value in a group.
Demo on DB Fiddle
Query
SELECT
t.*
FROM
test AS t
JOIN
(
SELECT
StartDate,
idcustomer,
MIN(total) AS lowest_total
FROM test
GROUP BY
StartDate,
idcustomer
) AS dt
ON dt.StartDate = t.StartDate AND
dt.idcustomer = t.idcustomer AND
dt.lowest_total = t.total;
Result
| StartDate | idcustomer | StartDateC | StartDateR | total |
| ---------- | ---------- | ---------- | ---------- | ----- |
| 2018-11-06 | 20 | 2018-11-06 | 2018-10-01 | 36 |
| 2018-10-01 | 20 | 2018-10-01 | 2018-10-01 | 0 |
| 2018-08-30 | 20 | 2018-08-30 | 2018-01-01 | 241 |
| 2018-10-01 | 62 | 2018-10-01 | 2018-10-01 | 0 |
| 2018-08-30 | 62 | 2018-08-30 | 2018-01-01 | 241 |
Now, you can create the view using this query (without the user defined variables).
CREATE VIEW test_view AS
SELECT
t.*
FROM
test AS t
JOIN
(
SELECT
StartDate,
idcustomer,
MIN(total) AS lowest_total
FROM test
GROUP BY
StartDate,
idcustomer
) AS dt
ON dt.StartDate = t.StartDate AND
dt.idcustomer = t.idcustomer AND
dt.lowest_total = t.total
answered Nov 23 '18 at 15:02
Chowkidar Madhur BhaiyaChowkidar Madhur Bhaiya
19.8k62336
1
Madhur, Thank you very much!!
– Steve Scan
Nov 24 '18 at 0:15
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%2f53435170%2fmysql-query-that-only-give-the-first-occurrence-of-each-group-that-can-be-saved%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
It seems that the "first" row within a group of idcustomer and StartDate is defined as the one having the lowest total value.
We can determine the lowest total value for the specified group in a Derived Table. We will then JOIN this result-set back to the main table using the lowest total value, idcustomer and StartDate. This will give us the complete row against the lowest total value in a group.
Demo on DB Fiddle
Query
SELECT
t.*
FROM
test AS t
JOIN
(
SELECT
StartDate,
idcustomer,
MIN(total) AS lowest_total
FROM test
GROUP BY
StartDate,
idcustomer
) AS dt
ON dt.StartDate = t.StartDate AND
dt.idcustomer = t.idcustomer AND
dt.lowest_total = t.total;
Result
| StartDate | idcustomer | StartDateC | StartDateR | total |
| ---------- | ---------- | ---------- | ---------- | ----- |
| 2018-11-06 | 20 | 2018-11-06 | 2018-10-01 | 36 |
| 2018-10-01 | 20 | 2018-10-01 | 2018-10-01 | 0 |
| 2018-08-30 | 20 | 2018-08-30 | 2018-01-01 | 241 |
| 2018-10-01 | 62 | 2018-10-01 | 2018-10-01 | 0 |
| 2018-08-30 | 62 | 2018-08-30 | 2018-01-01 | 241 |
Now, you can create the view using this query (without the user defined variables).
CREATE VIEW test_view AS
SELECT
t.*
FROM
test AS t
JOIN
(
SELECT
StartDate,
idcustomer,
MIN(total) AS lowest_total
FROM test
GROUP BY
StartDate,
idcustomer
) AS dt
ON dt.StartDate = t.StartDate AND
dt.idcustomer = t.idcustomer AND
dt.lowest_total = t.total
answered Nov 23 '18 at 15:02
Chowkidar Madhur BhaiyaChowkidar Madhur Bhaiya
19.8k62336
1
Madhur, Thank you very much!!
– Steve Scan
Nov 24 '18 at 0:15
add a comment |
It seems that the "first" row within a group of idcustomer and StartDate is defined as the one having the lowest total value.
We can determine the lowest total value for the specified group in a Derived Table. We will then JOIN this result-set back to the main table using the lowest total value, idcustomer and StartDate. This will give us the complete row against the lowest total value in a group.
Demo on DB Fiddle
Query
SELECT
t.*
FROM
test AS t
JOIN
(
SELECT
StartDate,
idcustomer,
MIN(total) AS lowest_total
FROM test
GROUP BY
StartDate,
idcustomer
) AS dt
ON dt.StartDate = t.StartDate AND
dt.idcustomer = t.idcustomer AND
dt.lowest_total = t.total;
Result
| StartDate | idcustomer | StartDateC | StartDateR | total |
| ---------- | ---------- | ---------- | ---------- | ----- |
| 2018-11-06 | 20 | 2018-11-06 | 2018-10-01 | 36 |
| 2018-10-01 | 20 | 2018-10-01 | 2018-10-01 | 0 |
| 2018-08-30 | 20 | 2018-08-30 | 2018-01-01 | 241 |
| 2018-10-01 | 62 | 2018-10-01 | 2018-10-01 | 0 |
| 2018-08-30 | 62 | 2018-08-30 | 2018-01-01 | 241 |
Now, you can create the view using this query (without the user defined variables).
CREATE VIEW test_view AS
SELECT
t.*
FROM
test AS t
JOIN
(
SELECT
StartDate,
idcustomer,
MIN(total) AS lowest_total
FROM test
GROUP BY
StartDate,
idcustomer
) AS dt
ON dt.StartDate = t.StartDate AND
dt.idcustomer = t.idcustomer AND
dt.lowest_total = t.total
answered Nov 23 '18 at 15:02
Chowkidar Madhur BhaiyaChowkidar Madhur Bhaiya
19.8k62336
1
Madhur, Thank you very much!!
– Steve Scan
Nov 24 '18 at 0:15
add a comment |
It seems that the "first" row within a group of idcustomer and StartDate is defined as the one having the lowest total value.
We can determine the lowest total value for the specified group in a Derived Table. We will then JOIN this result-set back to the main table using the lowest total value, idcustomer and StartDate. This will give us the complete row against the lowest total value in a group.
Demo on DB Fiddle
Query
SELECT
t.*
FROM
test AS t
JOIN
(
SELECT
StartDate,
idcustomer,
MIN(total) AS lowest_total
FROM test
GROUP BY
StartDate,
idcustomer
) AS dt
ON dt.StartDate = t.StartDate AND
dt.idcustomer = t.idcustomer AND
dt.lowest_total = t.total;
Result
| StartDate | idcustomer | StartDateC | StartDateR | total |
| ---------- | ---------- | ---------- | ---------- | ----- |
| 2018-11-06 | 20 | 2018-11-06 | 2018-10-01 | 36 |
| 2018-10-01 | 20 | 2018-10-01 | 2018-10-01 | 0 |
| 2018-08-30 | 20 | 2018-08-30 | 2018-01-01 | 241 |
| 2018-10-01 | 62 | 2018-10-01 | 2018-10-01 | 0 |
| 2018-08-30 | 62 | 2018-08-30 | 2018-01-01 | 241 |
Now, you can create the view using this query (without the user defined variables).
CREATE VIEW test_view AS
SELECT
t.*
FROM
test AS t
JOIN
(
SELECT
StartDate,
idcustomer,
MIN(total) AS lowest_total
FROM test
GROUP BY
StartDate,
idcustomer
) AS dt
ON dt.StartDate = t.StartDate AND
dt.idcustomer = t.idcustomer AND
dt.lowest_total = t.total
answered Nov 23 '18 at 15:02
Chowkidar Madhur BhaiyaChowkidar Madhur Bhaiya
19.8k62336
It seems that the "first" row within a group of idcustomer and StartDate is defined as the one having the lowest total value.
We can determine the lowest total value for the specified group in a Derived Table. We will then JOIN this result-set back to the main table using the lowest total value, idcustomer and StartDate. This will give us the complete row against the lowest total value in a group.
Demo on DB Fiddle
Query
SELECT
t.*
FROM
test AS t
JOIN
(
SELECT
StartDate,
idcustomer,
MIN(total) AS lowest_total
FROM test
GROUP BY
StartDate,
idcustomer
) AS dt
ON dt.StartDate = t.StartDate AND
dt.idcustomer = t.idcustomer AND
dt.lowest_total = t.total;
Result
| StartDate | idcustomer | StartDateC | StartDateR | total |
| ---------- | ---------- | ---------- | ---------- | ----- |
| 2018-11-06 | 20 | 2018-11-06 | 2018-10-01 | 36 |
| 2018-10-01 | 20 | 2018-10-01 | 2018-10-01 | 0 |
| 2018-08-30 | 20 | 2018-08-30 | 2018-01-01 | 241 |
| 2018-10-01 | 62 | 2018-10-01 | 2018-10-01 | 0 |
| 2018-08-30 | 62 | 2018-08-30 | 2018-01-01 | 241 |
Now, you can create the view using this query (without the user defined variables).
CREATE VIEW test_view AS
SELECT
t.*
FROM
test AS t
JOIN
(
SELECT
StartDate,
idcustomer,
MIN(total) AS lowest_total
FROM test
GROUP BY
StartDate,
idcustomer
) AS dt
ON dt.StartDate = t.StartDate AND
dt.idcustomer = t.idcustomer AND
dt.lowest_total = t.total
answered Nov 23 '18 at 15:02
Chowkidar Madhur BhaiyaChowkidar Madhur Bhaiya
19.8k62336
answered Nov 23 '18 at 15:02
Chowkidar Madhur BhaiyaChowkidar Madhur Bhaiya
19.8k62336
answered Nov 23 '18 at 15:02
Chowkidar Madhur BhaiyaChowkidar Madhur Bhaiya
19.8k62336
answered Nov 23 '18 at 15:02
Chowkidar Madhur BhaiyaChowkidar Madhur Bhaiya
19.8k62336
19.8k62336
1
Madhur, Thank you very much!!
– Steve Scan
Nov 24 '18 at 0:15
add a comment |
1
Madhur, Thank you very much!!
– Steve Scan
Nov 24 '18 at 0:15
1
1
Madhur, Thank you very much!!
– Steve Scan
Nov 24 '18 at 0:15
Madhur, Thank you very much!!
– Steve Scan
Nov 24 '18 at 0:15
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%2f53435170%2fmysql-query-that-only-give-the-first-occurrence-of-each-group-that-can-be-saved%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
Please edit the question and change table to proper formatting. You can use tablesgenerator.com/text_tables Although it would be best, if you can setup a db-fiddle.com on what you are trying to do.
– Chowkidar Madhur Bhaiya
Nov 22 '18 at 17:42