1

The following query works fine

SELECT
*,
(SELECT
GROUP_CONCAT(url SEPARATOR '$$' )
FROM project_photos
WHERE project_id = projects.id
ORDER BY priority) AS images
FROM projects
WHERE catID = 2
LIMIT 0,5

but the images column are not ordered as priority. I am unable to understand why it is happening

Structure for table project

CREATE TABLE `projects` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`catID` int(11) NOT NULL,
`title` varchar(255) NOT NULL,
`description` varchar(400) NOT NULL,
`url` varchar(255) DEFAULT NULL,
`tags` varchar(255) DEFAULT NULL,
`featured` varchar(3) NOT NULL DEFAULT 'No',
`featured_url` varchar(255) DEFAULT NULL,
`order` int(11) DEFAULT NULL,
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `catID` (`catID`),
CONSTRAINT `FK_catID` FOREIGN KEY (`catID`) REFERENCES `category` (`catID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=48 DEFAULT CHARSET=latin1;

Structure for table project_photos

CREATE TABLE `project_photos` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`url` varchar(250) DEFAULT NULL,
`project_id` int(11) DEFAULT NULL,
`priority` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=347 DEFAULT CHARSET=utf8;
  • 2
    The ORDER BY is in the inline view, not the outermost query. That's why. – Philᵀᴹ Oct 17 '12 at 9:07
  • perfect...... :) – M Khalid Junaid Oct 17 '12 at 9:29
2

How to Order GROUP_CONCAT() Values in MySQL

SELECT
*,
(SELECT
GROUP_CONCAT(url SEPARATOR '$$' ORDER BY priority)
FROM project_photos
WHERE project_id = projects.id
) AS images
FROM projects
WHERE catID = 2
LIMIT 0,5;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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