How to use GROUP BY and result with highest id value Announcing the arrival of Valued...
What is the meaning of 'breadth' in breadth first search?
Did Mueller's report provide an evidentiary basis for the claim of Russian govt election interference via social media?
Is there any word for a place full of confusion?
How much damage would a cupful of neutron star matter do to the Earth?
Significance of Cersei's obsession with elephants?
Aligning an equation at multiple points, with both left and right alignment, as well as equals sign alignment
Putting class ranking in CV, but against dept guidelines
If Windows 7 doesn't support WSL, then what does Linux subsystem option mean?
Quadrilaterals with equal sides
How to dry out epoxy resin faster than usual?
Interpretation of R output from Cohen's Kappa
How to run automated tests after each commit?
Did any compiler fully use 80-bit floating point?
How often does castling occur in grandmaster games?
What happened to Thoros of Myr's flaming sword?
Most bit efficient text communication method?
Project Euler #1 in C++
Trademark violation for app?
How does the secondary effect of the Heat Metal spell interact with a creature resistant/immune to fire damage?
"Lost his faith in humanity in the trenches of Verdun" — last line of an SF story
How were pictures turned from film to a big picture in a picture frame before digital scanning?
How to plot logistic regression decision boundary?
Can a new player join a group only when a new campaign starts?
How could we fake a moon landing now?
How to use GROUP BY and result with highest id value
Announcing the arrival of Valued Associate #679: Cesar Manara
Planned maintenance scheduled April 23, 2019 at 00:00UTC (8:00pm US/Eastern)Postgres: Use result of group by as column namesSelect query - group by and sub group byHow to filter specific entries from a MySql tableHow to filter maximum results entriesFind Max(value) for each department using GROUP BY MYSQLHow to get max value of union query?MIN(column) with subqueryCan I make this multiple join query faster?Using group by with left join and IN yields empty resultHow to transposing rows into columns in MySQL?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
I used the following query to get the result
SELECT `subjects`.*,`staffs`.`name`
FROM `subjects`
LEFT JOIN `staffs` ON `staffs`.`uid` = `subjects`.`teacher`
LEFT JOIN `subject_student_selective`
ON `subject_student_selective`.`course` = `subjects`.`classid`
AND `subject_student_selective`.`subject` = `subjects`.`subject`
AND `subject_student_selective`.`student` = '12947'
WHERE `subjects`.`classid` = '26'
AND `subjects`.`active` = '1'
AND (`subjects`.`type`='normal'
OR `subject_student_selective`.`id`>0
)
GROUP BY `subjects`.`subject`
ORDER BY `subjects`.`sort_order`
This gave the result but, I need to get the result with highest ID'ed Group By subject.
mysql group-by greatest-n-per-group
edited Jan 27 '17 at 18:47
Rick James
43.9k22360
asked Jan 27 '17 at 12:53
Jithin U. AhmedJithin U. Ahmed
11715
bumped to the homepage by Community♦ 4 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I used the following query to get the result
SELECT `subjects`.*,`staffs`.`name`
FROM `subjects`
LEFT JOIN `staffs` ON `staffs`.`uid` = `subjects`.`teacher`
LEFT JOIN `subject_student_selective`
ON `subject_student_selective`.`course` = `subjects`.`classid`
AND `subject_student_selective`.`subject` = `subjects`.`subject`
AND `subject_student_selective`.`student` = '12947'
WHERE `subjects`.`classid` = '26'
AND `subjects`.`active` = '1'
AND (`subjects`.`type`='normal'
OR `subject_student_selective`.`id`>0
)
GROUP BY `subjects`.`subject`
ORDER BY `subjects`.`sort_order`
This gave the result but, I need to get the result with highest ID'ed Group By subject.
mysql group-by greatest-n-per-group
edited Jan 27 '17 at 18:47
Rick James
43.9k22360
asked Jan 27 '17 at 12:53
Jithin U. AhmedJithin U. Ahmed
11715
bumped to the homepage by Community♦ 4 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
1
add max(uid) in the select
– moudiz
Jan 27 '17 at 12:58
Unless subject, classid, active is a candidate key of subjects, your query will produce indeterministic results.
– Lennart
Mar 26 '18 at 7:56
add a comment |
I used the following query to get the result
SELECT `subjects`.*,`staffs`.`name`
FROM `subjects`
LEFT JOIN `staffs` ON `staffs`.`uid` = `subjects`.`teacher`
LEFT JOIN `subject_student_selective`
ON `subject_student_selective`.`course` = `subjects`.`classid`
AND `subject_student_selective`.`subject` = `subjects`.`subject`
AND `subject_student_selective`.`student` = '12947'
WHERE `subjects`.`classid` = '26'
AND `subjects`.`active` = '1'
AND (`subjects`.`type`='normal'
OR `subject_student_selective`.`id`>0
)
GROUP BY `subjects`.`subject`
ORDER BY `subjects`.`sort_order`
This gave the result but, I need to get the result with highest ID'ed Group By subject.
mysql group-by greatest-n-per-group
edited Jan 27 '17 at 18:47
Rick James
43.9k22360
asked Jan 27 '17 at 12:53
Jithin U. AhmedJithin U. Ahmed
11715
I used the following query to get the result
SELECT `subjects`.*,`staffs`.`name`
FROM `subjects`
LEFT JOIN `staffs` ON `staffs`.`uid` = `subjects`.`teacher`
LEFT JOIN `subject_student_selective`
ON `subject_student_selective`.`course` = `subjects`.`classid`
AND `subject_student_selective`.`subject` = `subjects`.`subject`
AND `subject_student_selective`.`student` = '12947'
WHERE `subjects`.`classid` = '26'
AND `subjects`.`active` = '1'
AND (`subjects`.`type`='normal'
OR `subject_student_selective`.`id`>0
)
GROUP BY `subjects`.`subject`
ORDER BY `subjects`.`sort_order`
This gave the result but, I need to get the result with highest ID'ed Group By subject.
mysql group-by greatest-n-per-group
mysql group-by greatest-n-per-group
edited Jan 27 '17 at 18:47
Rick James
43.9k22360
asked Jan 27 '17 at 12:53
Jithin U. AhmedJithin U. Ahmed
11715
edited Jan 27 '17 at 18:47
Rick James
43.9k22360
asked Jan 27 '17 at 12:53
Jithin U. AhmedJithin U. Ahmed
11715
edited Jan 27 '17 at 18:47
Rick James
43.9k22360
edited Jan 27 '17 at 18:47
Rick James
43.9k22360
edited Jan 27 '17 at 18:47
Rick James
43.9k22360
43.9k22360
asked Jan 27 '17 at 12:53
Jithin U. AhmedJithin U. Ahmed
11715
asked Jan 27 '17 at 12:53
Jithin U. AhmedJithin U. Ahmed
11715
asked Jan 27 '17 at 12:53
Jithin U. AhmedJithin U. Ahmed
11715
11715
bumped to the homepage by Community♦ 4 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 4 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
1
add max(uid) in the select
– moudiz
Jan 27 '17 at 12:58
Unless subject, classid, active is a candidate key of subjects, your query will produce indeterministic results.
– Lennart
Mar 26 '18 at 7:56
add a comment |
1
add max(uid) in the select
– moudiz
Jan 27 '17 at 12:58
Unless subject, classid, active is a candidate key of subjects, your query will produce indeterministic results.
– Lennart
Mar 26 '18 at 7:56
1
1
add max(uid) in the select
– moudiz
Jan 27 '17 at 12:58
add max(uid) in the select
– moudiz
Jan 27 '17 at 12:58
Unless subject, classid, active is a candidate key of subjects, your query will produce indeterministic results.
– Lennart
Mar 26 '18 at 7:56
Unless subject, classid, active is a candidate key of subjects, your query will produce indeterministic results.
– Lennart
Mar 26 '18 at 7:56
add a comment |
1 Answer
1
active
oldest
votes
For the one row with the highest ID:
ORDER BY ID DESC LIMIT 1
For the "latest" information associated with each subject, the problem is considerably more complex. Search for "groupwise max". Here's one link:
http://mysql.rjweb.org/doc.php/groupwise_max
answered Jan 27 '17 at 18:46
Rick JamesRick James
43.9k22360
Or use MySQL8 window function
– Xenos
Jun 29 '18 at 9:22
@Xenos - Yes. (and MariaDB 10.2) How about presenting an Answer with a clean example of using a Windowing function for groupwise max.
– Rick James
Jun 29 '18 at 16:19
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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%2fdba.stackexchange.com%2fquestions%2f162430%2fhow-to-use-group-by-and-result-with-highest-id-value%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
For the one row with the highest ID:
ORDER BY ID DESC LIMIT 1
For the "latest" information associated with each subject, the problem is considerably more complex. Search for "groupwise max". Here's one link:
http://mysql.rjweb.org/doc.php/groupwise_max
answered Jan 27 '17 at 18:46
Rick JamesRick James
43.9k22360
Or use MySQL8 window function
– Xenos
Jun 29 '18 at 9:22
@Xenos - Yes. (and MariaDB 10.2) How about presenting an Answer with a clean example of using a Windowing function for groupwise max.
– Rick James
Jun 29 '18 at 16:19
add a comment |
For the one row with the highest ID:
ORDER BY ID DESC LIMIT 1
For the "latest" information associated with each subject, the problem is considerably more complex. Search for "groupwise max". Here's one link:
http://mysql.rjweb.org/doc.php/groupwise_max
answered Jan 27 '17 at 18:46
Rick JamesRick James
43.9k22360
Or use MySQL8 window function
– Xenos
Jun 29 '18 at 9:22
@Xenos - Yes. (and MariaDB 10.2) How about presenting an Answer with a clean example of using a Windowing function for groupwise max.
– Rick James
Jun 29 '18 at 16:19
add a comment |
For the one row with the highest ID:
ORDER BY ID DESC LIMIT 1
For the "latest" information associated with each subject, the problem is considerably more complex. Search for "groupwise max". Here's one link:
http://mysql.rjweb.org/doc.php/groupwise_max
answered Jan 27 '17 at 18:46
Rick JamesRick James
43.9k22360
For the one row with the highest ID:
ORDER BY ID DESC LIMIT 1
For the "latest" information associated with each subject, the problem is considerably more complex. Search for "groupwise max". Here's one link:
http://mysql.rjweb.org/doc.php/groupwise_max
answered Jan 27 '17 at 18:46
Rick JamesRick James
43.9k22360
answered Jan 27 '17 at 18:46
Rick JamesRick James
43.9k22360
answered Jan 27 '17 at 18:46
Rick JamesRick James
43.9k22360
answered Jan 27 '17 at 18:46
Rick JamesRick James
43.9k22360
43.9k22360
Or use MySQL8 window function
– Xenos
Jun 29 '18 at 9:22
@Xenos - Yes. (and MariaDB 10.2) How about presenting an Answer with a clean example of using a Windowing function for groupwise max.
– Rick James
Jun 29 '18 at 16:19
add a comment |
Or use MySQL8 window function
– Xenos
Jun 29 '18 at 9:22
@Xenos - Yes. (and MariaDB 10.2) How about presenting an Answer with a clean example of using a Windowing function for groupwise max.
– Rick James
Jun 29 '18 at 16:19
Or use MySQL8 window function
– Xenos
Jun 29 '18 at 9:22
Or use MySQL8 window function
– Xenos
Jun 29 '18 at 9:22
@Xenos - Yes. (and MariaDB 10.2) How about presenting an Answer with a clean example of using a Windowing function for groupwise max.
– Rick James
Jun 29 '18 at 16:19
@Xenos - Yes. (and MariaDB 10.2) How about presenting an Answer with a clean example of using a Windowing function for groupwise max.
– Rick James
Jun 29 '18 at 16:19
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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%2fdba.stackexchange.com%2fquestions%2f162430%2fhow-to-use-group-by-and-result-with-highest-id-value%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
1
add max(uid) in the select
– moudiz
Jan 27 '17 at 12:58
Unless subject, classid, active is a candidate key of subjects, your query will produce indeterministic results.
– Lennart
Mar 26 '18 at 7:56