MYSQL query that only give the first occurrence of each group that can be saved as a View
up vote
1
down vote
favorite
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 at 16:37
Steve Scan
195
add a comment |
up vote
1
down vote
favorite
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 at 16:37
Steve Scan
195
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.
– Madhur Bhaiya
Nov 22 at 17:42
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
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 at 16:37
Steve Scan
195
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 at 16:37
Steve Scan
195
asked Nov 22 at 16:37
Steve Scan
195
edited Nov 23 at 2:11
asked Nov 22 at 16:37
Steve Scan
195
asked Nov 22 at 16:37
Steve Scan
195
asked Nov 22 at 16:37
Steve Scan
195
195
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.
– Madhur Bhaiya
Nov 22 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.
– Madhur Bhaiya
Nov 22 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.
– Madhur Bhaiya
Nov 22 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.
– Madhur Bhaiya
Nov 22 at 17:42
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
accepted
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 at 15:02
Madhur Bhaiya
19.3k62236
1
Madhur, Thank you very much!!
– Steve Scan
Nov 24 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
up vote
0
down vote
accepted
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 at 15:02
Madhur Bhaiya
19.3k62236
1
Madhur, Thank you very much!!
– Steve Scan
Nov 24 at 0:15
add a comment |
up vote
0
down vote
accepted
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 at 15:02
Madhur Bhaiya
19.3k62236
1
Madhur, Thank you very much!!
– Steve Scan
Nov 24 at 0:15
add a comment |
up vote
0
down vote
accepted
up vote
0
down vote
accepted
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 at 15:02
Madhur Bhaiya
19.3k62236
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 at 15:02
Madhur Bhaiya
19.3k62236
answered Nov 23 at 15:02
Madhur Bhaiya
19.3k62236
answered Nov 23 at 15:02
Madhur Bhaiya
19.3k62236
answered Nov 23 at 15:02
Madhur Bhaiya
19.3k62236
19.3k62236
1
Madhur, Thank you very much!!
– Steve Scan
Nov 24 at 0:15
add a comment |
1
Madhur, Thank you very much!!
– Steve Scan
Nov 24 at 0:15
1
1
Madhur, Thank you very much!!
– Steve Scan
Nov 24 at 0:15
Madhur, Thank you very much!!
– Steve Scan
Nov 24 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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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.
– Madhur Bhaiya
Nov 22 at 17:42