By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE IF NOT EXISTS `posts` (
`post_id` int(11) NOT NULL AUTO_INCREMENT,
`title` text,
PRIMARY KEY (`post_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
-- posts
INSERT INTO `posts` (`post_id`, `title`) VALUES
(1, 'Ajax Jquery Drag and Drop Shopping Cart using PHP Mysql'),
(2, 'Make PHP Hashtag system by using Regular Expression'),
(3, 'Ajax Jquery Column Sort with PHP & MySql'),
(4, 'Drag and drop Upload multiples File By Ajax JQuery PHP');
--
CREATE TABLE IF NOT EXISTS `post_likes` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user` int(11) NOT NULL,
`post` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=46 ;
--table
CREATE TABLE IF NOT EXISTS `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
--
INSERT INTO `user` (`id`, `name`) VALUES
(1, 'john'),
(2, 'jack');
WITH CTE AS (SELECT
posts.post_id,
posts.title,
COUNT(post_likes.id) AS likes,
GROUP_CONCAT(user.name SEPARATOR '|') AS liked
,ROW_NUMBER() OVER(ORDER BY COUNT(post_likes.id)) rn
FROM
posts
LEFT JOIN post_likes ON post_likes.post = posts.post_id
LEFT JOIN user ON post_likes.user = user.id
GROUP BY
posts.post_id)
SELECT post_id,title, likes,liked FROM CTE WHERE rn = 3
| post_id | title | likes | liked |
|---|---|---|---|
| 3 | Ajax Jquery Column Sort with PHP & MySql | 0 |
SELECT post_id,title, likes,liked FROM
(SELECT
posts.post_id,
posts.title,
COUNT(post_likes.id) AS likes,
GROUP_CONCAT(user.name SEPARATOR '|') AS liked
,ROW_NUMBER() OVER(ORDER BY COUNT(post_likes.id)) rn
FROM
posts
LEFT JOIN post_likes ON post_likes.post = posts.post_id
LEFT JOIN user ON post_likes.user = user.id
GROUP BY
posts.post_id) t1
WHERE rn = 3
| post_id | title | likes | liked |
|---|---|---|---|
| 3 | Ajax Jquery Column Sort with PHP & MySql | 0 |