MySQL Group By and Indeterminate Results
up vote
0
down vote
favorite
I've been researching about using MySQL Group By and Joins correctly to avoid Indeterminate values for non-aggregated values in other columns. Now, with my below query, how could I ensure the correct Vin Number is selected with the minimum test drive time when I'm grouping by the customer ID? Basically, how can I ensure I get the entire row associated with the minimum test drive time?
SELECT t1.ID AS CUSTOMERID, t1.carhash,
MIN(t1.testdrivetime) AS testdrivetime,
t2.vinnumber AS vinnumber
FROM TABLE1 t1
INNER JOIN TABLE2 t2 ON t2.vinnumber = t1.carhash
INNER JOIN (SELECT t1.ID, MIN(testdrivetime) AS testdrivetime
FROM TABLE1
GROUP BY t1.ID) f
ON f.ID = t1.ID AND f.testdrivetime = t1.testdrivetime
GROUP BY ID
Times in database:
|---------------------|------------------|-------------------------|
| ID | carhash | testdrivetime |
|---------------------|------------------|-------------------------|
| 12 | 345 | 2:22 |
|---------------------|------------------|-------------------------|
| 12 | 277 | 4:51 |
|---------------------|------------------|-------------------------|
Output with above query (Wrong car hash since it's indeterminant):
|---------------------|------------------|-------------------------|
| ID | carhash | testdrivetime |
|---------------------|------------------|-------------------------|
| 12 | 277 | 2:22 |
|---------------------|------------------|-------------------------|
Output I want: (with correct carhash from same row as minimum test drive time)
|---------------------|------------------|-------------------------|
| ID | carhash | testdrivetime |
|---------------------|------------------|-------------------------|
| 12 | 345 | 2:22 |
|---------------------|------------------|-------------------------|
mysql
edited Nov 12 at 8:33
fewlinesofcode
1,928515
asked Nov 12 at 8:17
duke
85
add a comment |
up vote
0
down vote
favorite
I've been researching about using MySQL Group By and Joins correctly to avoid Indeterminate values for non-aggregated values in other columns. Now, with my below query, how could I ensure the correct Vin Number is selected with the minimum test drive time when I'm grouping by the customer ID? Basically, how can I ensure I get the entire row associated with the minimum test drive time?
SELECT t1.ID AS CUSTOMERID, t1.carhash,
MIN(t1.testdrivetime) AS testdrivetime,
t2.vinnumber AS vinnumber
FROM TABLE1 t1
INNER JOIN TABLE2 t2 ON t2.vinnumber = t1.carhash
INNER JOIN (SELECT t1.ID, MIN(testdrivetime) AS testdrivetime
FROM TABLE1
GROUP BY t1.ID) f
ON f.ID = t1.ID AND f.testdrivetime = t1.testdrivetime
GROUP BY ID
Times in database:
|---------------------|------------------|-------------------------|
| ID | carhash | testdrivetime |
|---------------------|------------------|-------------------------|
| 12 | 345 | 2:22 |
|---------------------|------------------|-------------------------|
| 12 | 277 | 4:51 |
|---------------------|------------------|-------------------------|
Output with above query (Wrong car hash since it's indeterminant):
|---------------------|------------------|-------------------------|
| ID | carhash | testdrivetime |
|---------------------|------------------|-------------------------|
| 12 | 277 | 2:22 |
|---------------------|------------------|-------------------------|
Output I want: (with correct carhash from same row as minimum test drive time)
|---------------------|------------------|-------------------------|
| ID | carhash | testdrivetime |
|---------------------|------------------|-------------------------|
| 12 | 345 | 2:22 |
|---------------------|------------------|-------------------------|
mysql
edited Nov 12 at 8:33
fewlinesofcode
1,928515
asked Nov 12 at 8:17
duke
85
You got it, just a moment
– duke
Nov 12 at 8:20
Edited my original post, sorry about that.
– duke
Nov 12 at 8:33
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I've been researching about using MySQL Group By and Joins correctly to avoid Indeterminate values for non-aggregated values in other columns. Now, with my below query, how could I ensure the correct Vin Number is selected with the minimum test drive time when I'm grouping by the customer ID? Basically, how can I ensure I get the entire row associated with the minimum test drive time?
SELECT t1.ID AS CUSTOMERID, t1.carhash,
MIN(t1.testdrivetime) AS testdrivetime,
t2.vinnumber AS vinnumber
FROM TABLE1 t1
INNER JOIN TABLE2 t2 ON t2.vinnumber = t1.carhash
INNER JOIN (SELECT t1.ID, MIN(testdrivetime) AS testdrivetime
FROM TABLE1
GROUP BY t1.ID) f
ON f.ID = t1.ID AND f.testdrivetime = t1.testdrivetime
GROUP BY ID
Times in database:
|---------------------|------------------|-------------------------|
| ID | carhash | testdrivetime |
|---------------------|------------------|-------------------------|
| 12 | 345 | 2:22 |
|---------------------|------------------|-------------------------|
| 12 | 277 | 4:51 |
|---------------------|------------------|-------------------------|
Output with above query (Wrong car hash since it's indeterminant):
|---------------------|------------------|-------------------------|
| ID | carhash | testdrivetime |
|---------------------|------------------|-------------------------|
| 12 | 277 | 2:22 |
|---------------------|------------------|-------------------------|
Output I want: (with correct carhash from same row as minimum test drive time)
|---------------------|------------------|-------------------------|
| ID | carhash | testdrivetime |
|---------------------|------------------|-------------------------|
| 12 | 345 | 2:22 |
|---------------------|------------------|-------------------------|
mysql
edited Nov 12 at 8:33
fewlinesofcode
1,928515
asked Nov 12 at 8:17
duke
85
I've been researching about using MySQL Group By and Joins correctly to avoid Indeterminate values for non-aggregated values in other columns. Now, with my below query, how could I ensure the correct Vin Number is selected with the minimum test drive time when I'm grouping by the customer ID? Basically, how can I ensure I get the entire row associated with the minimum test drive time?
SELECT t1.ID AS CUSTOMERID, t1.carhash,
MIN(t1.testdrivetime) AS testdrivetime,
t2.vinnumber AS vinnumber
FROM TABLE1 t1
INNER JOIN TABLE2 t2 ON t2.vinnumber = t1.carhash
INNER JOIN (SELECT t1.ID, MIN(testdrivetime) AS testdrivetime
FROM TABLE1
GROUP BY t1.ID) f
ON f.ID = t1.ID AND f.testdrivetime = t1.testdrivetime
GROUP BY ID
Times in database:
|---------------------|------------------|-------------------------|
| ID | carhash | testdrivetime |
|---------------------|------------------|-------------------------|
| 12 | 345 | 2:22 |
|---------------------|------------------|-------------------------|
| 12 | 277 | 4:51 |
|---------------------|------------------|-------------------------|
Output with above query (Wrong car hash since it's indeterminant):
|---------------------|------------------|-------------------------|
| ID | carhash | testdrivetime |
|---------------------|------------------|-------------------------|
| 12 | 277 | 2:22 |
|---------------------|------------------|-------------------------|
Output I want: (with correct carhash from same row as minimum test drive time)
|---------------------|------------------|-------------------------|
| ID | carhash | testdrivetime |
|---------------------|------------------|-------------------------|
| 12 | 345 | 2:22 |
|---------------------|------------------|-------------------------|
mysql
mysql
edited Nov 12 at 8:33
fewlinesofcode
1,928515
asked Nov 12 at 8:17
duke
85
edited Nov 12 at 8:33
fewlinesofcode
1,928515
asked Nov 12 at 8:17
duke
85
edited Nov 12 at 8:33
fewlinesofcode
1,928515
edited Nov 12 at 8:33
fewlinesofcode
1,928515
edited Nov 12 at 8:33
fewlinesofcode
1,928515
1,928515
asked Nov 12 at 8:17
duke
85
asked Nov 12 at 8:17
duke
85
asked Nov 12 at 8:17
duke
85
85
You got it, just a moment
– duke
Nov 12 at 8:20
Edited my original post, sorry about that.
– duke
Nov 12 at 8:33
add a comment |
You got it, just a moment
– duke
Nov 12 at 8:20
Edited my original post, sorry about that.
– duke
Nov 12 at 8:33
You got it, just a moment
– duke
Nov 12 at 8:20
You got it, just a moment
– duke
Nov 12 at 8:20
Edited my original post, sorry about that.
– duke
Nov 12 at 8:33
Edited my original post, sorry about that.
– duke
Nov 12 at 8:33
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
- In a sub-select query (Derived Table), determine the minimum
testdrivetimevalue for eachID. - Join this result-set back to the main table using
IDandtestdrivetime, to get the row corresponding to minimumtestdrivetimevalue.
Try:
SELECT
t.*
FROM your_table AS t
JOIN
(
SELECT
ID,
MIN(testdrivetime) AS min_testdrivetime
FROM your_table
GROUP BY ID
) AS dt
ON dt.ID = t.ID AND
dt.min_testdrivetime = t.testdrivetime
answered Nov 12 at 9:11
Madhur Bhaiya
19.3k62236
oh, so I wouldn't need to do anything else with t2.vinnumber to get the correct row with that as well?
– duke
Nov 12 at 18:01
@duke no. give it a try. From your question, it is unclear that what is the usage ofvinnumber
– Madhur Bhaiya
Nov 12 at 18:02
For the t2.vinnumber, I have to Join two tables together with t1.carhash and t2.vinnumber to get the correct number for the car. Even though I got the minimum test drive time, the vin number value is indeterminant since I haven't specified what row to get it from. It isn't a Min or Max vin number either since the numbers will range.
– duke
Nov 12 at 18:31
@duke can you provide a db-fiddle.com ?
– Madhur Bhaiya
Nov 12 at 18:32
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',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53258165%2fmysql-group-by-and-indeterminate-results%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
- In a sub-select query (Derived Table), determine the minimum
testdrivetimevalue for eachID. - Join this result-set back to the main table using
IDandtestdrivetime, to get the row corresponding to minimumtestdrivetimevalue.
Try:
SELECT
t.*
FROM your_table AS t
JOIN
(
SELECT
ID,
MIN(testdrivetime) AS min_testdrivetime
FROM your_table
GROUP BY ID
) AS dt
ON dt.ID = t.ID AND
dt.min_testdrivetime = t.testdrivetime
answered Nov 12 at 9:11
Madhur Bhaiya
19.3k62236
oh, so I wouldn't need to do anything else with t2.vinnumber to get the correct row with that as well?
– duke
Nov 12 at 18:01
@duke no. give it a try. From your question, it is unclear that what is the usage ofvinnumber
– Madhur Bhaiya
Nov 12 at 18:02
For the t2.vinnumber, I have to Join two tables together with t1.carhash and t2.vinnumber to get the correct number for the car. Even though I got the minimum test drive time, the vin number value is indeterminant since I haven't specified what row to get it from. It isn't a Min or Max vin number either since the numbers will range.
– duke
Nov 12 at 18:31
@duke can you provide a db-fiddle.com ?
– Madhur Bhaiya
Nov 12 at 18:32
add a comment |
up vote
0
down vote
- In a sub-select query (Derived Table), determine the minimum
testdrivetimevalue for eachID. - Join this result-set back to the main table using
IDandtestdrivetime, to get the row corresponding to minimumtestdrivetimevalue.
Try:
SELECT
t.*
FROM your_table AS t
JOIN
(
SELECT
ID,
MIN(testdrivetime) AS min_testdrivetime
FROM your_table
GROUP BY ID
) AS dt
ON dt.ID = t.ID AND
dt.min_testdrivetime = t.testdrivetime
answered Nov 12 at 9:11
Madhur Bhaiya
19.3k62236
oh, so I wouldn't need to do anything else with t2.vinnumber to get the correct row with that as well?
– duke
Nov 12 at 18:01
@duke no. give it a try. From your question, it is unclear that what is the usage ofvinnumber
– Madhur Bhaiya
Nov 12 at 18:02
For the t2.vinnumber, I have to Join two tables together with t1.carhash and t2.vinnumber to get the correct number for the car. Even though I got the minimum test drive time, the vin number value is indeterminant since I haven't specified what row to get it from. It isn't a Min or Max vin number either since the numbers will range.
– duke
Nov 12 at 18:31
@duke can you provide a db-fiddle.com ?
– Madhur Bhaiya
Nov 12 at 18:32
add a comment |
up vote
0
down vote
up vote
0
down vote
- In a sub-select query (Derived Table), determine the minimum
testdrivetimevalue for eachID. - Join this result-set back to the main table using
IDandtestdrivetime, to get the row corresponding to minimumtestdrivetimevalue.
Try:
SELECT
t.*
FROM your_table AS t
JOIN
(
SELECT
ID,
MIN(testdrivetime) AS min_testdrivetime
FROM your_table
GROUP BY ID
) AS dt
ON dt.ID = t.ID AND
dt.min_testdrivetime = t.testdrivetime
answered Nov 12 at 9:11
Madhur Bhaiya
19.3k62236
- In a sub-select query (Derived Table), determine the minimum
testdrivetimevalue for eachID. - Join this result-set back to the main table using
IDandtestdrivetime, to get the row corresponding to minimumtestdrivetimevalue.
Try:
SELECT
t.*
FROM your_table AS t
JOIN
(
SELECT
ID,
MIN(testdrivetime) AS min_testdrivetime
FROM your_table
GROUP BY ID
) AS dt
ON dt.ID = t.ID AND
dt.min_testdrivetime = t.testdrivetime
answered Nov 12 at 9:11
Madhur Bhaiya
19.3k62236
answered Nov 12 at 9:11
Madhur Bhaiya
19.3k62236
answered Nov 12 at 9:11
Madhur Bhaiya
19.3k62236
answered Nov 12 at 9:11
Madhur Bhaiya
19.3k62236
19.3k62236
oh, so I wouldn't need to do anything else with t2.vinnumber to get the correct row with that as well?
– duke
Nov 12 at 18:01
@duke no. give it a try. From your question, it is unclear that what is the usage ofvinnumber
– Madhur Bhaiya
Nov 12 at 18:02
For the t2.vinnumber, I have to Join two tables together with t1.carhash and t2.vinnumber to get the correct number for the car. Even though I got the minimum test drive time, the vin number value is indeterminant since I haven't specified what row to get it from. It isn't a Min or Max vin number either since the numbers will range.
– duke
Nov 12 at 18:31
@duke can you provide a db-fiddle.com ?
– Madhur Bhaiya
Nov 12 at 18:32
add a comment |
oh, so I wouldn't need to do anything else with t2.vinnumber to get the correct row with that as well?
– duke
Nov 12 at 18:01
@duke no. give it a try. From your question, it is unclear that what is the usage ofvinnumber
– Madhur Bhaiya
Nov 12 at 18:02
For the t2.vinnumber, I have to Join two tables together with t1.carhash and t2.vinnumber to get the correct number for the car. Even though I got the minimum test drive time, the vin number value is indeterminant since I haven't specified what row to get it from. It isn't a Min or Max vin number either since the numbers will range.
– duke
Nov 12 at 18:31
@duke can you provide a db-fiddle.com ?
– Madhur Bhaiya
Nov 12 at 18:32
oh, so I wouldn't need to do anything else with t2.vinnumber to get the correct row with that as well?
– duke
Nov 12 at 18:01
oh, so I wouldn't need to do anything else with t2.vinnumber to get the correct row with that as well?
– duke
Nov 12 at 18:01
@duke no. give it a try. From your question, it is unclear that what is the usage of
vinnumber– Madhur Bhaiya
Nov 12 at 18:02
@duke no. give it a try. From your question, it is unclear that what is the usage of
vinnumber– Madhur Bhaiya
Nov 12 at 18:02
For the t2.vinnumber, I have to Join two tables together with t1.carhash and t2.vinnumber to get the correct number for the car. Even though I got the minimum test drive time, the vin number value is indeterminant since I haven't specified what row to get it from. It isn't a Min or Max vin number either since the numbers will range.
– duke
Nov 12 at 18:31
For the t2.vinnumber, I have to Join two tables together with t1.carhash and t2.vinnumber to get the correct number for the car. Even though I got the minimum test drive time, the vin number value is indeterminant since I haven't specified what row to get it from. It isn't a Min or Max vin number either since the numbers will range.
– duke
Nov 12 at 18:31
@duke can you provide a db-fiddle.com ?
– Madhur Bhaiya
Nov 12 at 18:32
@duke can you provide a db-fiddle.com ?
– Madhur Bhaiya
Nov 12 at 18:32
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53258165%2fmysql-group-by-and-indeterminate-results%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
You got it, just a moment
– duke
Nov 12 at 8:20
Edited my original post, sorry about that.
– duke
Nov 12 at 8:33