0

I have the following SQL query:

SELECT bug.`id`,
       Max(report.`date`),
       Count(report.`id`),
       Max(version.`code`),
       bug.`id`
FROM   `bug`
       LEFT OUTER JOIN `stacktrace`
                    ON ( stacktrace.`bug_id` = bug.`id` )
       CROSS JOIN `version`
                  LEFT OUTER JOIN `report`
                               ON ( report.`stacktrace_id` = stacktrace.`id` )
WHERE  stacktrace.`version_id` = version.`id`
GROUP  BY bug.`id`
ORDER  BY Max(report.`date`) DESC; 

I now want to select version.name instead of version.code from the row where version.code is maximal. Is this possible? If so, how do I do this with minimal amount of queries/overhead?


Relevant Tables (stripped):

CREATE TABLE `bug` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
);

INSERT INTO `bug` VALUES();
INSERT INTO `bug` VALUES();

CREATE TABLE `version` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` int(11) NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `stacktrace` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bug_id` int(11) NOT NULL,
  `version_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_s_v` (`version_id`),
  KEY `FK_s_b` (`bug_id`),
  CONSTRAINT `FK_s_b` FOREIGN KEY (`bug_id`) REFERENCES `bug` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_s_v` FOREIGN KEY (`version_id`) REFERENCES `version` (`id`) ON DELETE CASCADE
);

CREATE TABLE `report` (
  `id` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `date` datetime NOT NULL,
  `stacktrace_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_r_s` (`stacktrace_id`),
  CONSTRAINT `FK_r_s` FOREIGN KEY (`stacktrace_id`) REFERENCES `stacktrace` (`id`) ON DELETE CASCADE
);

As SQLFiddle

0

1 Answer 1

0

In your case you can simply exchange the column names, but remember that text sorts differently then integer 1.10 comes before 1.2, so if you get such numbers you have to check and change your approach. see https://lists.mysql.com/mysql/183349

SELECT bug.`id`,
       Max(report.`date`),
       Count(report.`id`),
       Max(version.`name`),
       bug.`id`
FROM   `bug`
       LEFT OUTER JOIN `stacktrace`
                    ON ( stacktrace.`bug_id` = bug.`id` )
       CROSS JOIN `version`
                  LEFT OUTER JOIN `report`
                               ON ( report.`stacktrace_id` = stacktrace.`id` )
WHERE  stacktrace.`version_id` = version.`id`
GROUP  BY bug.`id`
ORDER  BY Max(report.`date`) DESC; 
3
  • My fiddle example didn't represent this accurately, but version.name is not guaranteed to follow any kind of schema, so text sorting is out of question for me. I've updated the fiddle link to give a better example.
    – F43nd1r
    Oct 20, 2020 at 16:07
  • that will not wirk, you need some kind of order to determine which value to take or an GROUP'_CONCAT., basically i told you already, that you need some alhorothm that decides take this but not the 10 others. for example 1.1.0 woulb be tter displayed as three columns if integer to sort them perfectly
    – nbk
    Oct 20, 2020 at 17:18
  • I have an order, it is version.code. I just want to get version.name from that row instead.
    – F43nd1r
    Oct 20, 2020 at 17:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.