Need a unique gift idea?
A Pastebin account makes a great Christmas gift
SHARE
TWEET

Untitled

a guest Aug 10th, 2018 59 Never
Upgrade to PRO!
ENDING IN00days00hours00mins00secs
 
  1. MySql query error, can anyone equivalent query of this?
  2. select posts.id, posts.title
  3. from posts
  4. inner join (
  5.     select post_id,max(created)
  6.     from comments
  7.     group by post_id
  8.     order by max(created) DESC ) as foo
  9. on posts.id=foo.post_id
  10. order by foo.max(created) DESC;
  11.    
  12. ERROR 1630 (42000): FUNCTION foo.max does not exist.
  13. Check the 'Function Name Parsing and Resolution' section in the Reference Manual
  14.    
  15. mysql> describe comments;
  16. +----------+--------------+------+-----+---------+----------------+
  17. | Field    | Type         | Null | Key | Default | Extra          |
  18. +----------+--------------+------+-----+---------+----------------+
  19. | id       | int(11)      | NO   | PRI | NULL    | auto_increment |
  20. | post_id  | int(11)      | NO   | MUL | NULL    |                |
  21. | name     | varchar(255) | NO   |     | NULL    |                |
  22. | email    | varchar(255) | NO   |     | NULL    |                |
  23. | body     | varchar(500) | NO   |     | NULL    |                |
  24. | mark     | tinyint(4)   | NO   |     | 1       |                |
  25. | created  | datetime     | YES  |     | NULL    |                |
  26. | modified | datetime     | YES  |     | NULL    |                |
  27. +----------+--------------+------+-----+---------+----------------+
  28. 8 rows in set (0.00 sec)
  29.  
  30.  
  31.  
  32. mysql> describe posts;
  33. +-------------+--------------+------+-----+---------+----------------+
  34. | Field       | Type         | Null | Key | Default | Extra          |
  35. +-------------+--------------+------+-----+---------+----------------+
  36. | id          | int(11)      | NO   | PRI | NULL    | auto_increment |
  37. | user_id     | int(11)      | NO   |     | NULL    |                |
  38. | title       | varchar(255) | NO   |     | NULL    |                |
  39. | body        | text         | YES  |     | NULL    |                |
  40. | category_id | int(11)      | NO   |     | NULL    |                |
  41. | tags        | varchar(50)  | NO   |     | NULL    |                |
  42. | mark        | tinyint(4)   | NO   |     | 1       |                |
  43. | created     | datetime     | YES  |     | NULL    |                |
  44. | modified    | datetime     | YES  |     | NULL    |                |
  45. +-------------+--------------+------+-----+---------+----------------+
  46. 9 rows in set (0.00 sec)
  47.    
  48. select posts.id, posts.title
  49. from posts
  50. inner join (
  51.     select post_id, max(created) as most_recent
  52.     from comments
  53.     group by post_id) as foo
  54. on posts.id=foo.post_id
  55. order by most_recent DESC;
  56.    
  57. foo.max(created)
  58.    
  59. max(foo.created)
  60.    
  61. select posts.id, posts.title
  62. from posts
  63. inner join
  64. (
  65.   select post_id,max(created) AS created
  66.   from comments
  67.   group by post_id
  68. ) as foo
  69. on posts.id=foo.post_id
  70. order by created DESC; <-- you don't need max
  71.    
  72. SELECT posts.id,
  73.        posts.title
  74. FROM   posts
  75.        INNER JOIN (SELECT post_id,
  76.                           created
  77.                    FROM   comments
  78.                    GROUP  BY post_id
  79.                    ORDER  BY Max(created) DESC) AS foo
  80.          ON posts.id = foo.post_id
  81. ORDER  BY Max(foo.created) DESC;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top